Codeigniter update_batch increment value

Question

Please correct me if I'm wrong, I am trying to update multiple rows, incrementing its value, from what I know, it is impossible to do this using update_batch() as CI always escape the value when using update_batch(), is this correct, or if there is a way, please elaborate the way as an answer. For example, I have this array:

$array = array(
    array(
        'product_id' => '1',
        'value' => '5',
    ),
    array(
        'product_id' => '2',
        'value' => '15'
    )
);

Now I want to update my table by incrementing column value(INT) by the value in the array where column product_id = product_id in the array.

What I'm doing now is using loops of set like this

foreach($array as $a) {
    $this->db->set('value' + $a['value'], FALSE);
    $this->db->where('product_id', $a['product_id'];
    $this->db->update('table');
}

However sometimes I will need to update more than 10 rows, and I'm thinking this process will be costly and take a lot of memory, how do i further simplify this method to get the same result ?

All answers are greatly welcome and appreciated. Thank you in advance.

Answer 1

You can use $this->db->update_batch(); to update multiple record at once. See this codeigniter user guide for update query.

Answer 2

IN CODEIGNITER 3/4 There is no solution for update batch arithmetic operations for self field, thus you need to edit the system file as it can be risky but may work for you, open file named db_query_builder.php in path system\database\db_query_builder.php and go to line number 1970, now replace with below code.

//if field name in value and +,-,*,/,% in value then apply that to self field
$flag = TRUE;
$then = 'THEN ';
if(is_array($val[$field]['value']) && count($val[$field]['value']) == 2)
{
    /* The $val[$field]['value'][0] 
       and $val[$field]['value'][1] are unidentical type so we are 
       converting it to string by removing the single quote and using 
       settype for string type.
    */
    $first = str_replace("'", "", $val[$field]['value'][0]);
    $second = str_replace("'", "", $val[$field]['value'][1]);
    settype($first, "string");
    settype($second, "string");
    switch ($first) {
        case "+":
            $then .= $val[$field]['field'].' + ';
            break;
        case '-':
            $then .= $val[$field]['field'].' - ';
            break;
        case "*":
            $then .= $val[$field]['field'].' * ';
            break;
        case '/':
            $then .= $val[$field]['field'].' / ';
            break;
        case '%':
            $then .= $val[$field]['field'].' % ';
            break;
        
        default:
            $flag = FALSE;
            break;
    }

    if(is_numeric($second) && $flag == TRUE)
    {
        $then .= $val[$field]['value'][1];
    }

}
else
{
    $then = 'THEN '.$val[$field]['value'];
}
$final[$val[$field]['field']][] = 'WHEN '.$val[$index]['field'].' = '.$val[$index]['value'].' '.$then;

//$final[$val[$field]['field']][] = 'WHEN '.$val[$index]['field'].' = '.$val[$index]['value'].' THEN '.$val[$field]['value'];

Now when defining the array for update_batch just pass the value array with arithmetic operator and amount.

$values[] = array('id' => 1,'balance' => ['+',500]); //will increment.

In above code you are identifying the value array and validating with the arithmetic sign and performing self field calculation.

Laravel also not support of update batch arithmetic operations for self field but there is github repo : https://github.com/mavinoo/laravelBatch#example-increment--decrement