insert in to TIMESTAMP field using ActiveRecords

[eluser]ningoo[/eluser]
I am trying to insert record in to profile table (see below) which has a column called 'lastlogin'.

The problem : CI inserts '0000-00-00 00:00:00' when i try to insert new record using active records.

my apologies , if i'm missing something thats obvious.

btw , thanks in advance

here's code

Code:

$profile_data = array(
              'member_id' => $member_id ,
              'membername' => $membername ,
              'profiledescription' => '' ,
              'lastlogin' => now(),
              'status' => 1,
                );
        
        $MAINDB->insert('profiles', $profile_data);

and heres table schema

Code:

create table `profiles` (
    `id` double ,
    `member_id` double ,
    `membername` varchar (96),
    `profiledescription` blob ,
    `created` timestamp ,
    `lastlogin` timestamp ,
    `status` double
);

[eluser]Michael Wales[/eluser]
The now() function within the date helper returns a Unix timestamp. First, I highly, highly, recommend you use Unix timestamps instead of MySQL timestamps - it will make your life much easier.

If you insist though... try this:

Code:

$profile_data = array(
              'member_id' => $member_id ,
              'membername' => $membername ,
              'profiledescription' => '' ,
              'lastlogin' => date('Y-m-d H:i:s', now()),
              'status' => 1,
                );

PHP's date() function accepts a string representing a format for the date and an optional Unix timestamp, for the timestamp you would like to convert into the strings representation.

[eluser]ningoo[/eluser]
"First, I highly, highly, recommend you use Unix timestamps"

Do you mean converting type of lastlogin column to INTEGER from current TIMESTAMP type.

How easy it would be to use "order by" and >= <= type queries ?

btw , Thanks a lot for fast reply.

[eluser]ningoo[/eluser]
Quick google search & found this :

http://simonwillison.net/2003/Jul/11/sto...esInMySQL/

[eluser]easymind[/eluser]
Is your problem that things like NOW() are interpreted like a string in active records? So the date is not set to NOW but to 0. I suspect there is no solution in active records. You should just write your own sql.

Code:

$sql = "INSERT INTO mytable SET timestamp=NOW()";
$this->db->query($sql);

active records does:

Code:

INSERT INTO mytable SET timestamp='NOW()'    <-quotes

Or maybe you can edit or extend the database lib and catch the values NOW() and CURRENT_TIMESTAMP().

[eluser]Michael Wales[/eluser]
ningoo - comparison and order by operator work exactly the same.

easymind - good catch, I thought he was using the NOW() function from the date helper rather than MySQL's NOW() function.