General developer forum

 
 
Me
Databases: execute_sql() not working in 2.0
 

How can I run an update query in moodle 2.0

$sql = "update tbl set field = 'new record' where id = USERID";

I tried  execute_sql($sql)  but it did not work

 
Average of ratings: -
Me
Re: Databases: execute_sql() not working in 2.0
 

Does anyone have an example of how to use  update_record() in moodle 2.0?

I read the docs, but not sure what how I setup a object with the data or how I set the WHERE id = X  part either.

My sql string is  "update tbl_name set field = '12345' where id = '77777'";

I assume it is suppoed to be  update_record("tbl_name",SOMETHING HERE)  but I don't know how to build the object or what to do next.

THANKS!

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: execute_sql() not working in 2.0
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Well, your example can actually be done with set_field:

$DB->set_field('tbl', 'field', 'new record', array('id' => USERID));

But if you wanted to set multiple columns at once, update_record would be better:

$updatedrecord = new stdClass();
$updatedrecord->id = USERID;
$updatedrecord->field = 'new record';
$updatedrecord->field2 = 'something else';
$DB->update_record('tbl', $updatedrecord);

 
Average of ratings: -
Picture of Marin Sok
Re: Databases: execute_sql() not working in 2.0
 

Please help me.

I got the error code below.

Coding error detected, it must be fixed by a programmer: moodle_database::update_record_raw() id field must be specified.

I couldn't figure out the place to fill in the id field.

Here is my code.

$updatedrecord = new stdClass();
//$updatedrecord->id = $record->id;
$updatedrecord->username = $record->username;
$updatedrecord->firstname = $updatesql->firstname;
$updatedrecord->lastname = $updatesql->lastname;
$updatedrecord->email = $updatesql->email;
$DB->update_record('user', $updatedrecord);

 

Thanks, Nara.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: execute_sql() not working in 2.0
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

The error message tells you that ->id must be specified. You have commented out the line that sets it.

 
Average of ratings: -
Picture of Andrew Zoltay
Re: Databases: execute_sql() not working in 2.0
 

Hi Tim,

Do you know if there is a method to allow multiple column multiple row updates?
E.g.
UPDATE mytable
SET column1 = 'mytestdata1', column2='mytestdata2'
WHERE column3 like '%multiplerecords%';

i.e. this statement would update a two columns on more than one record.

Thanks!

Cheers,
Andy

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: execute_sql() not working in 2.0
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

In a case like this, none of the more specific $DB-> methods will do it, so you can use $DB->execute() to run arbitrary SQL.

 
Average of ratings:Useful (1)
Picture of Andrew Zoltay
Re: Databases: execute_sql() not working in 2.0
 

Thanks Tim!
I found it, but came across an error:
"Coding error detected, it must be fixed by a programmer: moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!"
But figured out the query can't be suffixed with a semi-colon.

It's works perfectly now.

 
Average of ratings:Useful (1)
Picture of Jerin das
Re: Databases: execute_sql() not working in 2.0
 

Hi Marin Sok,

  How did you solve that problrm? .Becuase i have the same problem

Thanks

Jerin

 
Average of ratings: -