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:
"UPDATE
usersSET
votes= '(votes + 1)' WHERE
id= '44'
"
Which doesn't run, but this SQL does do what I'm looking for:
"UPDATE
usersSET
votes= (votes + 1) WHERE
id= '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?
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.