Increment field of mysql database using codeigniter's active record syntax

Question

I have the following php-codeigniter script which attempts to increment a field of a record using active-record syntax:

$data = array('votes' => '(votes + 1)');
$this->db->where('id', $post['identifier']);
$this->db->update('users', $data);

This produces the following SQL:

"UPDATEusersSETvotes= '(votes + 1)' WHEREid= '44'"

Which doesn't run, but this SQL does do what I'm looking for: "UPDATEusersSETvotes= (votes + 1) WHEREid= '44'"` <--Note the lack of quotes around (votes + 1)

Does anyone know how to implement this type of query with codeigniter's active record syntax?

score 125 · Accepted Answer · edited Jan 13 '14 at 18:47

You can do as given below:

$this->db->where('id', $post['identifier']);
$this->db->set('votes', 'votes+1', FALSE);
$this->db->update('users');

The reason this works is because the third (optional) FALSE parameter tells CodeIgniter not to protect the generated query with backticks ('). This means that the generated SQL will be:

UPDATE users SET votes= votes + 1 WHERE id= '44'

If you notice, the backticks are removed from '(votes+1)', which produces the desired effect of incrementing the votes attribute by 1.

score 4 · Answer 2 · answered Jul 19 '13 at 03:56

4

$data = array('votes' => 'votes + 1');

foreach ($data as $key=>$val) {
    $this->db->set($key, $val, FALSE);
}

$this->db->where('id', $post['identifier']);
$this->db->update('users', $data);

answered Jul 19 '13 at 03:56

wanderer

41
1

You don't need the entire $data array again in the update clause. You need just parts that should still be escaped when running the query. So in essence you may want to have it as : ```$this->db->update('users', ['some_key'=>'some value']); ``` – Akah Mar 18 '19 at 22:46

score 0 · Answer 3 · edited Oct 01 '21 at 14:36

0

You can do as given below:

    public function increment_product_count(){
        $product_id=$this->input->post('product_id');
        $this->db->where('id', $product_id);
        $this->db->set('click_count', 'click_count+1', FALSE);
        $this->db->update('tbl_product');
    }

edited Oct 01 '21 at 14:36

marc_s

732,580
175
1,330
1,459

answered Jul 24 '19 at 07:36

Sani Kamal

1,208
16
26

Increment field of mysql database using codeigniter's active record syntax

3 Answers3

Linked

Related