Add a sentence to a MySQL statement without quotes using Active Record functions for CodeIgniter

Question

I wanted to update the string using active entries in codeigniter, and I only want to increase the value of the field (received_qty = received_qty +1), I realized that I can do this in regular sql, but I can not use active codeigniter entries

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

Does anyone know how to do this using active records?

Answer 1

This will work.

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

ActiveRecord avoids everything that fits in the set (), where () and many other methods. Where and the set can both take an additional third parameter $ escape, which is logical. Set it to FALSE, and CodeIgniter will not run anything, which means that the increment of your field will not be considered 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 will need to modify the WHERE clause so that you like it, but

Answer 3

status is set to zero (update)

 $this->db->set('IsCurrent', '0'); $this->db->where('AcademicYearID',$academicYearId); $this->db->update('academicyear'); 

Answer 4

I was going to ask a similar question a little differently, but the problem was the same: I needed to update the date with an interval ( expiry_date = expiry_date + interval 3 month ) and Phil Sturgeon's answer helped solve the problem.

However, I realized that you can still use an array for fields that may have quotation marks, which means you can write:

 $this->db->set('received_qty', 'received_qty + 1', FALSE); $this->db->set('expired_date', 'CURDATE() + INTERVAL 10 DAY', FALSE); //extra example 1 $update['received_date'] = date("Ymd"); $update['receiver'] = $receiver_name; //extra example 2 $this->db->where($where); $this->db->update('vrs_distribution', $update); 

Where $this->db->last_query() will be output:

 UPDATE `vrs_distribution` SET `received_qty` = received_qty + 1, `expiry_date` = CURDATE() + INTERVAL 10 DAY, #extra example 1 `received_date` = '2015-07-01 16:00:00', `receiver` = 'strike_noir', #extra example 2 WHERE #where clause with backticks 

Note that fields that use set() do not contain quotation marks and appear first. The rest of the request has the opposite steps (allowing CodeIgniter to protect the remaining fields ").

Answer 5

You look pretty close, in CI ActiveRecord (or in SQL) there is no "increment by one" command.

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