Codeigniter - Batch Update with Multiple Where Conditions

Asked 15/9, 2011 at 5:5 Answered 8/3, 2022 at 14:25

Solved codeigniter activerecord codeigniter-2

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

Ovi answered 15/9, 2011 at 5:5 Comment(0)

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)

Nahshunn answered 15/9, 2011 at 5:49 Comment(1)

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 – Copartner 12/9, 2012 at 19:28

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

Govea answered 10/6, 2016 at 14:31 Comment(7)

Remember, my second index is a static value – Govea 10/6, 2016 at 14:45

Wow dude you are an out of the box thinker. I would of never though of that. Thanks – Molecular 15/7, 2016 at 15:19

if only you could think of a way where the second index was dynamic too! – Virgel 28/6, 2017 at 13:33

it would be better if you include the third parameter of update_batch to the argument of your public function update_data – Phionna 10/1, 2018 at 9:16

It does not work, take a look at my forum post: forum.codeigniter.com/thread-68561.html – Govea 11/1, 2018 at 20:28

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. – Lotty 21/9, 2019 at 18:30

Your solution is even leaner, but it is better to change my code than to change CodeIgniter code, because the framework update is getting harder. That said, my solution is the best. – Govea 23/9, 2019 at 19:23

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 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.
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!

Lotty answered 21/9, 2019 at 18:38 Comment(0)

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

Maiolica answered 8/3, 2022 at 14:25 Comment(1)

Please edit your answer with additional information on what the code does. See How to answer. – Shatterproof 11/3, 2022 at 8:8

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Sources:

Recommended topics

Hot tags