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.
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';
Do you know if there is a method to allow multiple column multiple row updates?
SET column1 = 'mytestdata1', column2='mytestdata2'
WHERE column3 like '%multiplerecords%';
i.e. this statement would update a two columns on more than one record.
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.
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.