Databases: Update Multiple records in moodle 2.0.x

Databases: Update Multiple records in moodle 2.0.x

Stuart Buck -
回帖数：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?

平均分：Useful (1)
回复Stuart Buck

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

Tim Hunt -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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).

平均分：Useful (1)
回复Tim Hunt

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

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);

平均分： -
回复Mike Shurance

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

Tim Hunt -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 Plugin developers的头像

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

平均分： -
回复Tim Hunt

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

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.

平均分： -
回复Mike Shurance

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

Tim Hunt -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 Plugin developers的头像

That is the scary thing.

Please Google 'SQL injection' and educate yourself.

平均分： -
回复Tim Hunt

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

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); 

平均分： -
回复Mike Shurance

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

Tim Hunt -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 Plugin developers的头像

Yes. That is right.

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

平均分： -
回复Tim Hunt

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

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); 

 



平均分： -
回复Mike Shurance

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

Tim Hunt -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 Plugin developers的头像

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

平均分： -
回复Tim Hunt

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

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

平均分： -