Insert current date time using now in a field using MySQL PHP

Question

Since MySQL evidently cannot automatically insert the function now   in a datetime field in adding new records like some other databases  based on comments  I m explicitly trying to insert it using an SQL statement   People seem to think timestamp with curdate   is not the answer due to the various limitations of timestamp   There are numerous articles on the web suggesting inserting now   using SQL should work   When I try to insert the date time using the SQL statement  however  the field does not populate with the current time date  but it only gives me the default 0000-00-  etc  This is probably a syntax error  but it s driving me crazy  so I am posting it    mysql query  INSERT INTO users  first  last  whenadded  VALUES    first     last   now         It inserts first and last  but nothing for when added  leaving 0000-00-00  etc  in the whenadded field   The field type is datetime  it has no collation  attributes  null default or extra  BTW  I tried putting now   in single quotes    It threw an error

User · Answer

These both work fine for me      lt  php    db   mysql connect  localhost   user   pass      mysql select db  test db        stmt    INSERT INTO  test    first   last   whenadded   VALUES                   first      last    NOW         rslt   mysql query  stmt       stmt    INSERT INTO  users    first   last   whenadded   VALUES                   first       last    CURRENT TIMESTAMP       rslt   mysql query  stmt      gt    Side note  mysql query   is not the best way to connect to MySQL in current versions of PHP

User · Answer

Just go to the column whenadded and change the default value to CURRENT TIMESTAMP

User · Answer

The only reason I can think of is you are adding it as string  now     not function call now    Or whatever else typo   SELECT NOW      to see if it returns correct value

User · Answer

Like Pekka said  it should work this way  I can t reproduce the problem with this self-contained example    lt  php      pdo   new PDO  mysql host localhost dbname test charset utf8    localonly    localonly         pdo- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION        pdo- gt setAttribute PDO  ATTR EMULATE PREPARES  false         pdo- gt exec           CREATE TEMPORARY TABLE soFoo               id int auto increment              first int              last int              whenadded DATETIME              primary key id                         pdo- gt exec  INSERT INTO soFoo  first last whenadded  VALUES  0 1 Now            pdo- gt exec  INSERT INTO soFoo  first last whenadded  VALUES  0 2 Now            pdo- gt exec  INSERT INTO soFoo  first last whenadded  VALUES  0 3 Now            foreach   pdo- gt query  SELECT   FROM soFoo   PDO  FETCH ASSOC  as  row             echo join         row     n           Which  currently  prints  1   0   1   2012-03-23 16 00 18 2   0   2   2012-03-23 16 00 18 3   0   3   2012-03-23 16 00 18   And here s  almost  the same script using a TIMESTAMP field and DEFAULT CURRENT TIMESTAMP    lt  php      pdo   new PDO  mysql host localhost dbname test charset utf8    localonly    localonly         pdo- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION        pdo- gt setAttribute PDO  ATTR EMULATE PREPARES  false         pdo- gt exec           CREATE TEMPORARY TABLE soFoo               id int auto increment              first int              last int              whenadded TIMESTAMP DEFAULT CURRENT TIMESTAMP              primary key id                         pdo- gt exec  INSERT INTO soFoo  first last  VALUES  0 1          pdo- gt exec  INSERT INTO soFoo  first last  VALUES  0 2         sleep 1        pdo- gt exec  INSERT INTO soFoo  first last  VALUES  0 3          foreach   pdo- gt query  SELECT   FROM soFoo   PDO  FETCH ASSOC  as  row             echo join         row     n           Conveniently  the timestamp is converted to the same datetime string representation as in the first example - at least with my PHP PDO mysqlnd version

User · Answer

You forgot to close the mysql query command   mysql query  INSERT INTO users  first  last  whenadded  VALUES    first     last   now        Note that last parentheses

User · Answer

now     worked for me   but my field type is date only and yours is datetime  i am not sure if this is the case

User · Answer

What about SYSDATE       lt  php    db   mysql connect  localhost   user   pass      mysql select db  test db        stmt    INSERT INTO  test    first   last   whenadded   VALUES                   first      last    SYSDATE         rslt   mysql query  stmt     gt    Look at Difference between NOW    SYSDATE    amp  CURRENT DATE   in MySQL for more info about NOW   and SYSDATE

User · Answer

Currently  and with the new versions of Mysql can insert the current date automatically without adding a code in your PHP file  You can achieve that from Mysql while setting up your database as follows     Now  any new post will automatically get a unique date and time  Hope this can help

User · Answer

NOW   normally works in SQL statements and returns the date and time  Check if your database field has the correct type  datetime   Otherwise  you can always use the PHP date   function and insert   date  Y-m-d H i s     But I wouldn t recommend this

[php] Insert current date/time using now() in a field using MySQL/PHP

Examples related to php

Examples related to mysql

Examples related to datetime