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

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.

These both work fine for me...

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

?>

Side note: mysql_query() is not the best way to connect to MySQL in current versions of PHP.

Like Pekka said, it should work this way. I can't reproduce the problem with this self-contained example:

<?php
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo (
            id int auto_increment,
            first int,
            last int,
            whenadded DATETIME,
            primary key(id)
        )
    ');
    $pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,1,Now())');
    $pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,2,Now())');
    $pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,3,Now())');

    foreach( $pdo->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:

<?php
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo (
            id int auto_increment,
            first int,
            last int,
            whenadded TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            primary key(id)
        )
    ');
    $pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,1)');
    $pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,2)');
    sleep(1);
    $pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,3)');

    foreach( $pdo->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.

now()

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

You forgot to close the mysql_query command:

mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())");

Note that last parentheses.

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.

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?

Just go to the column whenadded and change the default value to CURRENT_TIMESTAMP

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.

What about SYSDATE() ?

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

Look at Difference between NOW(), SYSDATE() & CURRENT_DATE() in MySQL for more info about NOW() and SYSDATE().