Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
3

Im using jQuery datepicker the format of the datepicker is this 08/25/2012

i have errors when inserting to my database it insert only 0000-00-00 00 00 00

my codes is

<?php
$id = $_POST['id'];
$name = $_POST['name'];
$date = $_POST['date'];
$sql = mysql_query( "INSERT INTO user_date VALUE( '', '$name', '$date')" ) or die ( mysql_error() );
echo 'insert successful';
?>

im sure my insert is correct....

share|improve this question
2  
Please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is good tutorial. – Madara Uchiha Aug 25 '12 at 8:47

3 Answers

up vote 19 down vote accepted

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '08/25/2012' is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):

  1. Configure Datepicker to provide dates in a supported format using its dateFormat option:

    $( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" });

  2. Use MySQL's STR_TO_DATE() function to convert the string:

    INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))

  3. Convert the string received from jQuery into a PHP timestamp—e.g. using strtotime():

    $timestamp = strtotime($_POST['_date']);

    and then either:

    • format the timestamp using date():

      $date = date('Y-m-d', $timestamp);

    • pass the timestamp directly to MySQL using FROM_UNIXTIME():

      INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))

  4. Manually manipulate the string into a valid literal:

    $parts = explode('/', $_POST['date']);
$date  = "$parts[2]-$parts[0]-$parts[1]";

Warning

  1. Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables.

  2. Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions:

    This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

  3. You appear to be using either a DATETIME or TIMESTAMP column for holding a date value; I recommend you consider using MySQL's DATE type instead. As explained in The DATE, DATETIME, and TIMESTAMP Types:

    The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

    The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

    The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

share|improve this answer
thank you so much for this info – user1538668 Aug 25 '12 at 8:53
@eggyal +1 nice answer. – BANG Mar 19 at 6:55
up vote 1 down vote

You must make sure that the date format is YYYY-MM-DD on your jQuery output. I can see jQuery returns MM-DD-YYYY, which is not the valid MySQL date format and this is why it returns an error.

To convert it to the right one you could do this:

$dateFormated = split('/', $date);
$date = $dateFormated[2].'-'.$dateFormated[0].'-'.$dateFormated[1];

Then you will get formatted date that will be valid MySQL format, which is YYYY-MM-DD, i.e. 2012-08-25

I would also recommend using mysql_real_escape_string as you insert data into database to prevent SQL injections as a quick solution or better use PDO or MySQLi.

Your insert query using mysql_real_escape_string should rather look like this:

$sql = mysql_query( "INSERT INTO user_date VALUE( '', '" .mysql_real_escape_string($name). "', '" .mysql_real_escape_string($date). "'" ) or die ( mysql_error() );
share|improve this answer
up vote 0 down vote

You should consider creating a timestamp from that date witk mktime()

eg:

$date = explode('/', $_POST['date']);
$time = mktime(0,0,0,$date[0],$date[1],$date[2]);
$mysqldate = date( 'Y-m-d H:i:s', $time );
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.