Search code examples
phpmysqlarrayscodeignitersql-update

How to increment column values with CodeIgniter's update_batch()

Using CI update_batch(), I am unable to set a value where I increment an integer db value. This works successfully with CI set(); update(), but batch is the better option as there are multiple updates.

I have columns that all have the same characters at the end of the name, with different characters at the beginning (different years): 2014x , 2015x , 2016x , etc. So I have created a $var that identifies the year, then I add the string 'x' and 'y' via concatenation. Finally, the value setting in the array is to increment by 1 so I add +1 . This concatenation works fine in the keys - that is to say I am updating the correct column and fields. 

$data = array(
    array('name' => $name1,
            $var.'x' => $var.'x+1'),
    array('name' => $name2,
            $var.'y' => $var.'y+1')
            );          
$this->db->update_batch('my_table', $data, 'tname');

In the above case, the fields are updated with the value of only $var - the year that has been defined.

I have also tried the following:

=> '{"$var.x+1"}'  // places a '0' value in the field
=> $var.'x' +1       // places the value of $var
=> '$var.x+1'        // places a '0' value in the field

How can I use update_batch() to increment my field by 1?

As an example, this code works successfully:

$this->db->where('name',$name1);
$this->db->set($var.'x',$var.'x+1',FALSE);
$this->db->update('my_table');
Solution

  • You can not do this via update_batch.

    In the document https://ellislab.com/codeigniter/user-guide/database/active_record.html, its said like this:

    Note: All values are escaped automatically producing safer queries.

    how about just repeat db->set? e.g.

    $this->db->where('name',$name1);
for($var = 2004; $var<2008;$var++) {
   $this->db->set($var.'x',$var.'x+1',FALSE);
   $this->db->set($var.'y',$var.'y+1',FALSE);
}
$this->db->update('my_table');