For starters, the Codeigniter documentation on update_batch does not exist. kenjis was kind enough to provide some documentation and submit it to the repository. Hopefully they pull it soon.
Does anyone know how to add multiple where conditions to Codeigniters update_batch command?
My Desired Use:
$where = array(
'title',
'name'
);
$this->db->update_batch('mytable', $data, $where);
When I tried this code I got the follow error:
A Database Error Occurred
One or more rows submitted for batch updating is missing the specified index.
Filename: C:\wamp\www\wheel\system\database\DB_active_rec.php
Line Number: 1451
Update Batch Documentation by kenjis:
$this->db->update_batch();
Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:
$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');
// 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')
The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.
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');
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With