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

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

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

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

This is the easy way to handle timestamp insertion

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

    $data = array(
            'name' => $name ,
            'email' => $email,
            'time' =>date('Y-m-d H:i:s')
            );
            $this->db->insert('mytable', $data);

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.

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');

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();

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