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

Question

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

Out of the track and even scope but do you know you can get NOW() like effect using `strtotime`? For e.g. `echo date("N", strtotime('now'));` will give you today's date, refer to php.net/strtotime and php.net/date — Kumar, Jun 16 '11 at 05:09

score 81 · Answer 1 · answered Jun 16 '11 at 05:03

81

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

answered Jun 16 '11 at 05:03

Bill H

2,069
2
20
29

any way to use `now() + 1 day` somehow for active records ? – aspirinemaga Aug 27 '13 at 08:44
Is there any way this could trigger on an empty update ? – Edeph Jun 05 '14 at 04:43
1

Worked for me too, CI2.1.2 on year 2015 – fedmich Jan 14 '15 at 17:48
1

@AnwarHossain I am using version 3.1 and its working, there might be some other problem. – Muhammad Aug 23 '17 at 14:07
1

I'm using CI version 3.1.8 and it's working absolutely just fine :) – haidarvm May 29 '18 at 12:50
I was struggling to use sysdate in the Oracle database. this worked perfectly for me. **$this->db->set('UPDATED_AT', 'sysdate', false);** – Anil Chaudhari Jul 24 '23 at 05:01

score 34 · Accepted Answer · answered Jun 15 '11 at 07:32

34

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

answered Jun 15 '11 at 07:32

cwallenpoole

79,954
26
128
166

25

For simplicity I always set timestamps with date('Y-m-d H:i:s') with different ORMs etc, no need to fight against different implementations. – geekuality Jun 15 '11 at 07:47
2

Personally, I always use Unix timestamps. I know that 1308124173975 will always be a long (or int, or float, or equivalent) in every language and database. I like not having to care. – cwallenpoole Jun 15 '11 at 07:50
1

That's a good point. I'm lazy so I like having human readable entries if possible. :) – geekuality Jun 15 '11 at 07:52
1

@jupaju It's just a different type of laziness. I could remember how PgSQL, MySQL, Oracle, Java, PHP, and Python store dates, or I could just have everything be a gosh-darn number. And you know what? If it's a number, then it instantly translates to AS2, AS3, and JS. And when I need the actual date? Go to Chrome, Ctrl-shift-J and paste String(new Date(/*numeric timestamp*/)) – cwallenpoole Jun 15 '11 at 08:09
4

I'd like to note that using date('Y-m-d H:i:s') may have an issue where the MySQL server and PHP library are using two different timezones without you being aware. For instance I had a host who had MySQL on GMT (+0h) and PHP was EST (-5h). – bafromca Jan 17 '14 at 08:17

score 15 · Answer 3 · answered Nov 19 '13 at 12:10

15

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

answered Nov 19 '13 at 12:10

Bruno Rigolon

439
6
5

2

I'd like to note that according to the docs, this is the CodeIgniter "recommended way". I'd recommend using MySQL's native NOW() rather than PHP's date('Y-m-d H:i:s') if you're on a shared host as they might be set to different timezones. – bafromca Jan 17 '14 at 08:19
Yup, in theory you can't decide design time what your infrastructure will look like 3 years down the road. In reality, I can, often do, but shouldn't. – Herbert Van-Vliet Sep 24 '21 at 22:51

score 13 · Answer 4 · answered Jan 06 '14 at 04:53

13

This is the easy way to handle timestamp insertion

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

answered Jan 06 '14 at 04:53

saurabh kamble

1,510
2
25
42

score 8 · Answer 5 · answered Jan 02 '13 at 21:39

8

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.

answered Jan 02 '13 at 21:39

Steven

113
1
5

date() gives php warning not to rely on systems date – pankijs Feb 07 '15 at 14:00

Sandeep Sherpur · Answer 6 · 2018-07-20T08:56:32.153

8

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

edited Jul 20 '18 at 08:56

answered Aug 24 '16 at 09:45

Sandeep Sherpur

2,418
25
27

This answer has been flagged as low quality. If it answers the question, consider adding a bit of text to explain how it works. – lmo Aug 24 '16 at 22:37
3

Worked for me. Explanation might help, but it did solve my problem. – Jordan Jun 15 '18 at 22:41
time() is not needed, date() always return current time – Loki Jul 18 '18 at 14:04

score 4 · Answer 7 · answered Aug 29 '14 at 14:50

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

score 3 · Answer 8 · edited Apr 27 '17 at 21:25

3

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

edited Apr 27 '17 at 21:25

Marco Bonelli

63,369
21
118
128

answered Jan 05 '14 at 01:35

Brandon Hollenbeck

51
3

I think its a dead link – Anwar Hossain Jul 10 '16 at 17:37

score 3 · Answer 9 · answered Feb 26 '12 at 19:16

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

score 0 · Answer 10 · answered Apr 21 '15 at 11:43

0

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

answered Apr 21 '15 at 11:43

Max

1,528
21
33

score 0 · Answer 11 · answered May 12 '16 at 05:48

0

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

answered May 12 '16 at 05:48

khalrd

60
6

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

11 Answers11

Linked

Related