CODEWITHSUNDEEP
codeigniteractiverecordcodeigniter-2
zechdc
zechdc

Reputation: 3404

Codeigniter - Batch Update with Multiple Where Conditions

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:

Upvotes: 16

Views: 38548

Answers (4)

Rakesh Maurya
Rakesh Maurya

Reputation: 61

Try this one also !
Suppon 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');

If this helpfull give a thumbs up

Upvotes: 0

Ray Li
Ray Li

Reputation: 7919

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:

  1. Use the 4th batch_size parameter of update_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.
  2. 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!

Upvotes: 0

Rodrigo Prazim
Rodrigo Prazim

Reputation: 858

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

Upvotes: 12

Chris Schmitz
Chris Schmitz

Reputation: 8247

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)

Upvotes: 19

Related Questions