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....
Introduction to MySQL DATE data type This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want. MySQL uses 3 bytes to store a DATE value.
The simplest method to insert the current date and time in MySQL is to use the now() function. Once you call the function, it returns the current date and time in the system's configured time zone as a string. The value returned from the now() function is YYYY-MM-DD for the date and HH-MM-SS-UU for the time record.
$d=mktime(11, 14, 54, 8, 12, 2014); echo "Created date is " . date("Y-m-d h:i:sa", $d);
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.
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
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
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 an altField
together with its altFormat
option:
<input type="hidden" id="actualDate" name="actualDate"/>
$( "selector" ).datepicker({ altField : "#actualDate" altFormat: "yyyy-mm-dd" });
Or, if you're happy for users to see the date in YYYY-MM-DD
format, simply set the dateFormat
option instead:
$( "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 something that PHP understands as a date, such as a DateTime
object:
$dt = \DateTime::createFromFormat('m/d/Y', $_POST['date']);
and then either:
obtain a suitable formatted string:
$date = $dt->format('Y-m-d');
obtain the UNIX timestamp:
$timestamp = $dt->getTimestamp();
which is then passed directly to MySQL's FROM_UNIXTIME()
function:
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]";
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.
Also, as stated in the introduction to the PHP manual chapter on the mysql_*
functions:
This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. 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.
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 displaysDATETIME
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.
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 );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With