Codeigniter - Batch Update with Multiple Where Conditions

Question

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.

Sources:

kenjis's documentation update: https://bitbucket.org/kenjis/ci-user-guide/changeset/3d579dd14afe
saintnicster's pull request: https://github.com/EllisLab/CodeIgniter/pull/448

Chris Schmitz · Accepted Answer · 2012-03-15 20:09:21Z

19

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)

edited Mar 15, 2012 at 20:09

answered Sep 15, 2011 at 5:49

Chris Schmitz

8,2876 gold badges36 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

taber Over a year ago

Bummer, it would be a nice addition to have a fourth param on the end. If you use straight up SQL, you can add an "AND" to the end of the UPDATE query. That seems to give the desired effect, eg: UPDATE mytable SET ... WHERE id IN(1,2,3) AND mycategory = 44

Hasta Dhana · Accepted Answer · 2019-07-15 13:27:13Z

12

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

edited Jul 15, 2019 at 13:27

Hasta Dhana

4,6947 gold badges21 silver badges28 bronze badges

answered Jun 10, 2016 at 14:31

Rodrigo Prazim

8688 silver badges15 bronze badges

6 Comments

Rodrigo Prazim Over a year ago

Remember, my second index is a static value

deviloper Over a year ago

if only you could think of a way where the second index was dynamic too!

John Christian De Chavez Over a year ago

it would be better if you include the third parameter of update_batch to the argument of your public function update_data

Rodrigo Prazim Over a year ago

It does not work, take a look at my forum post: forum.codeigniter.com/thread-68561.html

Ray Li Over a year ago

The reason the where clause is removed on subsequent batches is because the 'where' query is cleared in the batch loop. See my answer for an explanation of what is happening.

|

Nimantha · Accepted Answer · 2025-08-24 07:10:51Z

You have import data through csv/excel then store all record in single array like:

Array
(
    [0] => Array
        (
           
            [price] => 100.00
            [part_no] => PD001
            [brand] => 44
            [special_price] => 90.10

        )

    [1] => Array
        (
            
            [price] => 200.00
            [part_no] => PD002
            [special_price] => 150.00
           
        )

)

Step 2: Call to model

$model = new CatalogModel();
$result = $model->batchUpdateData($final_array);


function batchUpdateData($array = array()){

  $query = $this->db->table('product');
  $price = array();
  $part_no = array();
  $special = array();
  $brand = array();

  if (!empty($array)) {
   foreach ($array as $key => $value) {
    $price[] = $value['price'];
    $part_no[] = $value['part_no'];
    $special[] = $value['special_price'];
    $brand[] = $value['brand'];

  }
  
    $num = count($part_no);
    $sql ="UPDATE product SET "; 
   
   //  price colum update
     $sql .=" price = CASE ";
     for($i=0; $i < $num; $i++){
     $sql .=" WHEN  part_no = '".$part_no[$i]."' AND brand = '".$brand[$i]."'  THEN '".$price[$i]."' ";

   } 
    $sql .="ELSE price END ,";
    
    // special colum update
    $sql .=" special_price = CASE ";
    for($i=0; $i < $num; $i++){
    $sql .=" WHEN  part_no = '".$part_no[$i]."' AND brand = '".$brand[$i]."'  THEN '".$special[$i]."' ";

   } 
    $sql .="ELSE special_price END";
    

   $sql .=" WHERE part_no IN ('" . implode("','", $part_no) . "') ";

   return $this->db->query($sql);

 }

}

This will product query like:

UPDATE `product` SET `price` = CASE WHEN `part_no` = 'PD001'  and `brand` = '44' THEN '100.00' WHEN `part_no` = 'PD002'  and `brand` = '44' THEN '200.00' ELSE `price` END 
WHERE `part_no` IN('PD001','PD002');

Collectives™ on Stack Overflow

Codeigniter - Batch Update with Multiple Where Conditions

Sources:

3 Answers 3

1 Comment

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Sources:

3 Answers 3

1 Comment

6 Comments

Comments

Linked

Related