Exabytes beginner

Tuesday, January 6, 2015

PHP MySQL insert date

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

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

$( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" });
Use MySQL's STR_TO_DATE() function to convert the string:

INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))
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))
Manually manipulate the string into a valid literal:

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

1 comment:

  1. hi mc da.

    jalan jalan. jumpa blog kau. menyemak sikit.

    guna PHP strtotime kena berhati hati.
    kalau PHP 32bit. tarikh maksimum adalah 19 Jan 2038.
    tarikh ke depan dari itu, PHP akan return 01-01-1970.