How do I do batch updates?

How can开发者_如何转开发 I do batch updates in CodeIgniter instead of firing query each and every time in database?

Mysql can do multiple updates or inserts. Usually in an Active Record pattern you do inserts one by one, but for bulk updates or inserts you can do this.

$sql = "INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);";
$this->db->query($sql);

CodeIgniter's active record class has an insert_batch() method that does just this and takes care of escaping the data.

$data = array(
  array('name' => 'John', 'email' => 'john@email.com'),
  array('name' => 'Sue', 'email' => 'sue@email.com')
);

$this->db->insert_batch('my_table', $data);

http://codeigniter.com/user_guide/database/active_record.html

For the sake of other old-fashioned Code Igniter users (like myself):

You’re using an old version? insert_batch and update_batch have been around for over a year [Sep/2010]. If you’re not going to upgrade you would have to run an insert query for each row, or manually construct a batch insert statement.

From: http://codeigniter.com/forums/viewthread/188416/#891199

This question was about updates, but the accepted answer is for inserts.

Here is how you do a batch update:

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title'); 

This example is from the CodeIgniter user guide:

http://ellislab.com/codeigniter/user-guide/database/active_record.html#update