CODEWITHSUNDEEP
phpmysqldatetime
DomingoSL
DomingoSL

Reputation: 15504

Inserting a Date and Time value to a MySql from PHP

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

Upvotes: 1

Views: 3008

Answers (4)

OMGKurtNilsen
OMGKurtNilsen

Reputation: 5098

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)

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 158007

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

Upvotes: 0

Alexander Konstantinov
Alexander Konstantinov

Reputation: 5476

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.

Upvotes: 1

Mitch Dempsey
Mitch Dempsey

Reputation: 39939

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

Upvotes: 1

Related Questions