Databases: Update Multiple records in moodle 2.0.x

Databases: Update Multiple records in moodle 2.0.x

by Stuart Buck -
Number of replies: 11

Is there a method of updating multiple rows within the new $DB functionality. I can see update_record and update_record_raw, but both rely on a row id beind passed in the data object or the array params.

 

Can anyone advise how I can achieve updating multiple rows without getting each row and iterating through each record with a new transaction. That could be very resourse heavy.

I think I must be looking at the wrong docs or library.

 

also trying to find help docs on http://docs.moodle.org/en/Developer_FAQ#How_do_I_migrate_code_to_Moodle_2.0.3F i found that the link to the documentation for datalib.php is a total blank page. It doesn't produce a 404 so the page is their but not rendering. report dead links to?

Average of ratings: Useful (1)
In reply to Stuart Buck

Re: Databases: Update Multiple records in moodle 2.0.x

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

A lot of updates like that you can do with set_field, or set_field select.

But there are some things that you can only do with an urbitrary UPDATE query, and for those there is $DB->execute($sql).

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Stuart Buck -

Thanks Tim,

 

All working with set_field

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Mike Shurance -

When I run the $DB->execute($sql)  I get the following error.

Multiple sql statements found or bound parameters not used properly in query

Here is my code. 

$update_header = "update mdl_course_sections set summary = 'Course Name' where course = '" . $course . "' AND section = '0'";
 
$DB->execute($update_header);

Average of ratings: -
In reply to Mike Shurance

Re: Databases: Update Multiple records in moodle 2.0.x

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

A perfect excuse for http://xkcd.com/327/.

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Mike Shurance -

Tim,

Thanks for the reply, yet I am unsure of what that means. When echo out the SQL string, and run it in phpmyadmin, it runs correctly...so I assume the SQL string is formatted correctly.

Please explain further.

Average of ratings: -
In reply to Mike Shurance

Re: Databases: Update Multiple records in moodle 2.0.x

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

That is the scary thing.

Please Google 'SQL injection' and educate yourself.

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Mike Shurance -

Here is my designed queries...Are these better?

  $select_header_sql = "SELECT id FROM {course_sections} WHERE course = ? AND section = ?";
  $select_header_params = array($course,"0");
  $select_header_result = $DB->get_record_sql($select_header_sql, $select_header_params);
 
  $update_header_array = new stdClass();
  $update_header_array->id = $select_header_result->id;
  $update_header_array->summary = "COURSE HEADER";
   
  $DB->update_record('course_sections',$update_header_array); 

Average of ratings: -
In reply to Mike Shurance

Re: Databases: Update Multiple records in moodle 2.0.x

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Yes. That is right.

The first one could be done with $DB->get_record instead.

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Mike Shurance -

Could you please show me a brief example of get_record to get this data?

I tried this but it didn't seem to pull the ID field.

//FIND THE SECTION ID
  $select_section_result = $DB->get_record("course_sections", array("course" => " . $course . ", "section" => ". $i ."));

//UPDATE THE HEADER
  $update_header_array = new stdClass();
  $update_header_array->id = $select_header_result->id;
  $update_header_array->summary = "COURSE HEADER";
        $DB->update_record('course_sections',$update_header_array); 

 



Average of ratings: -
In reply to Mike Shurance

Re: Databases: Update Multiple records in moodle 2.0.x

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

$select_section_result = $DB->get_record("course_sections", array("course" => $course, "section" => $i));

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Update Multiple records in moodle 2.0.x

by Mike Shurance -

Tim,

Thanks for clarifiying everything. I thought that I needed to append the variable into the function. Your help has been GREAT!

Mike

Average of ratings: -