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
);
134872 次浏览
小开
最佳答案

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).

小开

I typically use triggers to handle timestamps but I think this may work.

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


$this->db->set('time', 'NOW()', FALSE);
$this->db->insert('mytable', $data);
小开

putting NOW() in quotes won't work as Active Records will put escape the NOW() into a string and tries to push it into the db as a string of "NOW()"... you will need to use 

$this->db->set('time', 'NOW()', FALSE);

to set it correctly.

you can always check your sql afterward with

$this->db->last_query();
小开

you can load the date helper and use the codeigniter interal now() if you want to reference the users GMT time offset

it woulk look somewhat like this

$data = array(
'created_on' => date('Y-m-d H:i:s',now())
);

If you don't use the GTM master settings and let your users set their own offsets there is no advantage over using php's time() function.

小开

aspirinemaga, just replace:

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

for it:

$this->db->set('time', 'NOW() + INTERVAL 1 DAY', FALSE);
$this->db->insert('mytable', $data);
小开

Using the date helper worked for me

$this->load->helper('date');

You can find documentation for date_helper here.

$data = array(
'created' => now(),
'modified' => now()
);


$this->db->insert('TABLENAME', $data);
小开

This is the easy way to handle timestamp insertion 

$data = array('created_on' => date('Y-m-d H:i:s'));
小开

According to the source code of codeigniter, the function set is defined as:

public function set($key, $value = '', $escape = TRUE)
{
$key = $this->_object_to_array($key);


if ( ! is_array($key))
{
$key = array($key => $value);
}


foreach ($key as $k => $v)
{
if ($escape === FALSE)
{
$this->ar_set[$this->_protect_identifiers($k)] = $v;
}
else
{
$this->ar_set[$this->_protect_identifiers($k, FALSE, TRUE)] = $this->escape($v);
}
}


return $this;
}

Apparently, if $key is an array, codeigniter will simply ignore the second parameter $value, but the third parameter $escape will still work throughout the iteration of $key, so in this situation, the following codes work (using the chain method):

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

However, this will unescape all the data, so you can break your data into two parts:

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

$this->db->query("update table_name set ts = now() where 1=1") also works for current time stamp!

小开

run query to get now() from mysql i.e select now() as nowinmysql;

then use codeigniter to get this and put in 

$data['created_on'] = $row->noinmyssql;
$this->db->insert($data);
小开 
    $data = array(
'name' => $name ,
'email' => $email,
'time' =>date('Y-m-d H:i:s')
);
$this->db->insert('mytable', $data);

提交答案