PHP MySQL syntax for inserting date,time

Question

I am trying to insert to date,time fields using a php script but I am getting a syntax error. Can someone please tell me, where I am doing the mistake. Thanks fellows

INSERT INTO calendar(event,from,to,day) VALUES ('".$_REQUEST['event']."', '".$_REQUEST['from_time']."', '".$_REQUEST['to_time']."', '".$_REQUEST['date_event']."')

Answer 1

To insert into mySQL datetime fields, the string has to be in a certain format for mySQL to understand it. The problem is that php has its own thoughts and ideas on how dates are represented. When dealing with dates between the two you have to translate.

If in php you are dealing with a time object you can do this to get a string mySQL will like:

$mysqlDateString = date('Y-m-d H:i:s', $phpdate);

or if you are dealing with a string date you can do this:

$mysqlDateString = date('Y-m-d H:i:s', $strtotime("08/09/2009"));

If you get a datetime string from mySQL you can do this to deal with it in PHP:

$phpTime = strtotime($mysqlDateString);

Just came across this problem myself, so hopefully this will work for you as well.

Answer 2

Never insert string data into your sql statement without sanitizing the data or you end up with sql injections (intentional or unintentional injections), see http://php.net/mysql_real_escape_string
If you do not have a debugger installed, let php print the sql statement so you can inspect it.

Format and indent your sql queries. They are much easier to read and debug that way.

Always check the return value of mysql_query(). If it's FALSE the query failed and mysql_errno() or mysql_error() can tell you more about the cause of the error.

If you want to use an identifier that is also a reserved word for MySQL you almost always must put it in backticks (`) or double_quotes (in ansi mode).

The format of date/time literals understood by MySQL is explained at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

Using _REQUEST for INSERT operations may be questionable ...but I leave that to others to answer ;-)

<?php
$mysql = mysql_connect...


$query = "
  INSERT INTO
    `calendar`
    (`event`, `from`, `to`, `day`)
  VALUES
    (
      '" . mysql_real_escape_string($_REQUEST['event'], $mysql) ."',
      '" . mysql_real_escape_string($_REQUEST['from_time'], $mysql) ."',
      '" . mysql_real_escape_string($_REQUEST['to_time'], $mysql) ."',
      '" . mysql_real_escape_string($_REQUEST['date_event'], $mysql]) ."'
    )
";
echo '<pre>$query=', htmlspecialchars($query), '</pre>';
$result = mysql_query($query, $mysql);
if ( !$result ) {
  echo 'error: ', mysql_error($mysql);
}

And btw: Use prepared statements instead.