Roman Roman - 6 months ago 42
SQL Question

Inserting NOW() into Database with CodeIgniter's Active Record

I want to insert current time in database using mySQL function NOW() in Codeigniter's active record. The following query won't work:

$data = array(
'name' => $name ,
'email' => $email,
'time' => NOW()
);
$this->db->insert('mytable', $data);


This is because CodeIgniter’s ActiveRecord class automatically escapes the input.

The following works fine, by calling set() and passing peratmeter FALSE, so that it doesn't escape the NOW().

$data = array(
'name' => $name ,
'email' => $email,
);
$this->db->set('time', 'NOW()', FALSE);
$this->db->insert('mytable', $data);


However, my question is that is there any other way than this? For example, if i can use somehow use by adding everything in the data array only?
For example, something like:

$data = array(
'name' => $name ,
'email' => $email,
'time' => NOW(), FALSE
);

Answer

Unless I am greatly mistaken, the answer is, "No, there is no way."

The basic problem in situations like that is the fact that you are calling a MySQL function and you're not actually setting a value. CI escapes values so that you can do a clean insert but it does not test to see if those values happen to be calling functions like aes_encrypt, md5, or (in this case) now(). While in most situations this is wonderful, for those situations raw sql is the only recourse.

On a side, date('Y-m-d'); should work as a PHP version of NOW() for MySQL. (It won't work for all versions of SQL though).