Add a clause to a MySQL statement without quotes using CodeIgniter's Active Record functions

Question

I wanted to update a row using active records in codeigniter, and I only want to increment a field value (received_qty = received_qty +1) , I realized that I can do that in usual sql, but I cant in codeigniter active records

$update['received_qty'] = 'received_qty + 1';
$update['received_date'] = date("Y-m-d");
$this->db->where($where);
$this->db->update('vrs_distribution', $update);

anyone know how to do it using active records?

Answer 1

This will work.

$this->db->set('received_qty', 'received_qty + 1', FALSE);
$this->db->set('received_date', date("Y-m-d"));
$this->db->where($where);
$this->db->update('vrs_distribution');

ActiveRecord escapes everything put into a set(), where() and many other methods. Where and set can both take an optional third parameter of $escape which is a boolean. Set it to FALSE and CodeIgniter will not escape anything, meaning your field increment wont be treated as a string.

Answer 2

Or you can do:

$this->db->query('UPDATE vrs_distribution SET received_qty = received_qty + 1, received_date = CURDATE() WHERE id = ' . $id);

You would need to modify WHERE clause to suit you though