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
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!
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);
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.
The error message tells you that ->id must be specified. You have commented out the line that sets it.
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
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.
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.
