Increment field of mysql database using codeigniter's active record syntax
Solution 1
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.
Solution 2
$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);
Casey Flynn
American software engineer living in Taipei, Taiwan. 只是一個住在台北的工程師. Mostly speaks English and 中文. 11100100 10111101 10100000 11100101 10100101 10111101 https://cflynn.us
Updated on October 02, 2021Comments
-
Casey Flynn over 2 years
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?
-
Akah about 5 yearsYou 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']);