Inserting a Date and Time value to a MySql from PHP

Question

Hello i want to send to a MySQL data base a date and time in a format compatible with the mysql DateTime format, wich is: 0000-00-00 00:00:00 ...

Im using this code who brings the date and time from a PHP command:

   $insert = "INSERT INTO sms (ref, texto, fecha)
   VALUES ('".$_POST['usuario']."', '".$_POST['sms']."', '".date(DATE_ATOM, mktime(0, 0, 0, 7, 1, 2000))."')";
   $add_member = mysql_query($insert);

I really want to do this but with a MySQL command, i mean, using the Date and Time from the mysql server. Thanks

Answer 1

Use NOW() when inserting the record INSERT INTO table (date) VALUES (NOW())

For your code:

$insert = "INSERT INTO sms (ref, texto, fecha)
VALUES ('".$_POST['usuario']."', '".$_POST['sms']."', NOW() )";
$add_member = mysql_query($insert);

Answer 2

MySQL allows only limited number of date formats provided as an input. From MySQL manual:

Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.

You're providing date in the format Y-m-d\TH:i:sP (value of the DATE_ATOM constant), which is not supported by MySQL (though it may be parsed and stored correctly depending on the current SQL mode).

You should use date('Y-m-d H:i:s', ...) instead.

P.S. And, yes, you should protect your code from SQL injections.

Answer 3

$date = date("Y-m-d H:i:s", mktime(0, 0, 0, 7, 1, 2000));

$usuario = mysql_real_escape_string($_POST['usuario']);
$sms = mysql_real_escape_string($_POST['sms']);
$date = mysql_real_escape_string($date);

$sql = "INSERT INTO sms (ref, texto, fecha) VALUES ('$usuario', '$sms', '$date')";
mysql_query($sql) or trigger_error(mysql_error().$sql);

Answer 4

First I want to tell you that you should follow the advice in the comments and read up on SQL injection.

This code will insert the record with the current time in the right format.

$insert = "INSERT INTO sms (ref, texto, fecha)
VALUES ('".$_POST['usuario']."', '".$_POST['sms']."', '".date('Y-m-d H:i:s')."' )";
$add_member = mysql_query($insert);

If you don't want the current time you need to change date('Y-m-d H:i:s') into date('Y-m-d H:i:s', some_unix_timestamp)