Codeigniter - Batch Update with Multiple Where Conditions
You can't add multiple where clauses to update_batch()
. It only accepts a string as the third parameter for the where clause so I'm sure there's no way to do this the way the method is currently written.
From the source:
/** * Update_Batch * * Compiles an update string and runs the query * * @param string the table to retrieve the results from * @param array an associative array of update values * @param string the where key * @return object */public function update_batch($table = '', $set = NULL, $index = NULL)
I am using codeigniter 3.1.5 and had the same problem, but I solved my problem as follows:
$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->where('name','My Name 2');$this->db->update_batch('mytable', $data, 'title');
Produces it:
// Produces:// UPDATE `mytable`// SET `name` = CASE// WHEN `title` = 'Another title' THEN 'Another Name 2'// WHEN `title` = 'My title' THEN 'My Name 2'// ELSE `name`// END,// `date` = CASE // WHEN `title` = 'My title' THEN 'My date 2'// WHEN `title` = 'Another title' THEN 'Another date 2'// ELSE `date`// END// WHERE `title` IN ('My title','Another title')// AND `name` = 'My Name 2'
UPDATE
I had a problem trying to add more than 100 records with update_batch, for example:
$data = [1=>a,2=>b ... 200=>zz];
First call (with WHERE):
// Produces:// UPDATE `mytable`// SET `name` = CASE// WHEN `title` = 'My title' THEN 'My Name 2'// WHEN `title` = 'Another title' THEN 'Another Name 2'// ELSE `name`// END,// `date` = CASE // WHEN `title` = 'My title' THEN 'My date 2'// WHEN `title` = 'Another title' THEN 'Another date 2'// ELSE `date`// END// WHERE `title` IN ('My title','Another title')// AND `name` = 'My Name 2'
Second call on (Without WHERE):
// Produces:// UPDATE `mytable`// SET `name` = CASE// WHEN `title` = 'My title' THEN 'My Name 2'// WHEN `title` = 'Another title' THEN 'Another Name 2'// ELSE `name`// END,// `date` = CASE // WHEN `title` = 'My title' THEN 'My date 2'// WHEN `title` = 'Another title' THEN 'Another date 2'// ELSE `date`// END// WHERE `title` IN ('My title','Another title')
Try this:
$chunk1 = array_chunk($data,100);for($i=0;$i < count($chunk1);$i++) { $this->upload_model->update_data($chunk1[$i],'My Name 2');}
Model:
public function update_data($data='',$name=''){ $this->db->where('name',$name); $this->db->update_batch('mytable', $data, 'title');}
Multiple where conditions are broken in update_batch
because the WHERE query is being cleared in the batch loop.
Here is the batch update loop:
for ($i = 0, $total = count($this->qb_set_ub); $i < $total; $i += $batch_size) { if ($this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set_ub, $i, $batch_size), $index))) { $affected_rows += $this->affected_rows(); } $this->qb_where = array(); }
Notice that the passed WHERE conditions are cleared by $this->qb_where = array();
.
In CodeIgniter v3.1.10, the offending line is on 1940 in DB_query_builder.php
. This produces a very unexpected behavior where WHERE conditions work for the first batch processed (default 100) and fail for subsequent batches.
There are two possible solutions:
- Use the 4th
batch_size
parameter ofupdate_batch
and pass a large number such as 100,000 so all the queries are processed in the first batch and the WHERE condition is not cleared. - Update the offending line to restore the initial WHERE conditions.
Code for Solution #2:
// Save initial where conditions. $where_holder = $this->qb_where; // Batch this baby $affected_rows = 0; for ($i = 0, $total = count($this->qb_set_ub); $i < $total; $i += $batch_size) { if ($this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set_ub, $i, $batch_size), $index))) { $affected_rows += $this->affected_rows(); } // Restore intial where conditions. $this->qb_where = $where_holder; }
Hope this helped!