PHP mysql插入日期格式 [英] PHP mysql insert date format
问题描述
我使用jQuery datepicker
datepicker的格式是这个 08/25/2012
我插入到我的数据库时有错误只插入0000-00-00 00 00 00
我的代码是
<?php
$ id = $ _POST ['id'];
$ name = $ _POST ['name'];
$ date = $ _POST ['date'];
$ sql = mysql_query(INSERT INTO user_date VALUE('','$ name','$ date'))或die(mysql_error());
echo'insert successful';
?>
确保我的插入是正确的....
如日期和时间文字:
MySQL识别
DATE
以下格式的值:
作为
'YYYY-MM-DD'
或'YY-MM-DD'
格式的字符串。允许使用轻松语法:任何标点符号可用作日期部分之间的分隔符。例如,'2012-12-31'
,'2012/12/31'
,'2012 ^ 12 ^ 31'
和'2012 @ 12 @ 31'
相当。
作为在
'YYYYMMDD'
或'YYMMDD'
格式中没有分隔符的字符串,条件是字符串作为日期有意义。例如,'20070523'
和'070523'
被解释为'2007-05- 23'
,但'071332'
是非法的(它具有无意义的月份和日期部分),成为'0000-00 -00'
。
作为
YYYYMMDD
或YYMMDD
格式,前提是数字作为日期有意义。例如,19830905
和830905
被解释为'1983-09-05'
。
因此,字符串 '08 / 25/2012'
不是有效的MySQL日期文字。您有四个选项(在某些模糊的优先顺序中,不需要任何进一步信息):
-
配置Datepicker以提供使用
altField
支持的格式的日期与其altFormat
选项:< input type =hiddenid =actualDatename =actualDate/>
$(selector).datepicker({
altField:#actualDate
altFormat:yyyy-mm-dd
});
或者,如果您很高兴用户在
YYYY中看到日期-MM-DD
格式,只需设置dateFormat
选项:$(selector).datepicker({
dateFormat: yyyy-mm-dd
});
-
使用MySQL的
STR_TO_DATE()
函数转换字符串:INSERT INTO user_date VALUES('','$ name',STR_TO_DATE('$ date','%m /%d /%Y'))
-
将从jQuery接收的字符转换为PHP理解为日期,例如
DateTime
object:$ dt = \DateTime :: createFromFormat('m / d / Y ',$ _POST ['date']);
然后:
-
获得一个合适的格式化字符串:
$ date = $ dt - >格式('Ym-d');
-
获取UNIX时间戳记:
< pre class =lang-php prettyprint-override>$ timestamp = $ dt-> getTimestamp();
然后直接传递给MySQL的
FROM_UNIXTIME()
功能:INSERT INTO user_date VALUES('','$ name',FROM_UNIXTIME($ timestamp))
-
-
将字符串手动操作为有效的文字:
$ parts = explode('/',$ _POST ['date']);
$ date =$ parts [2] - $ parts [0] - $ parts [1];
< h2>警告
-
您的代码容易受到SQL注入的攻击。</ strong> em>应该使用准备好的语句,您将变量作为不被SQL评估的参数传递到该语句中。如果你不知道我在说什么,或者如何解决它,请阅读 Bobby Tables 。
-
同样,如介绍到PHP手册章节的
mysql _ *
函数:
这个扩展名从PHP 5.5.0开始已被弃用,不建议用于编写新的代码,因为它将来会被删除。相反, mysqli 或PDO_MySQL 扩展名。另请参见 MySQL API概述,以便在选择MySQL API时进一步获得帮助。
-
您似乎正在使用
DATETIME
或持有日期值的TIMESTAMP
列;我建议您考虑使用MySQL的DATE
类型。如DATE
,DATETIME
和TIMESTAMP
类型:
DATE
类型用于具有日期部分但没有时间部分的值。 MySQL在'YYYY-MM-DD'
格式中检索并显示DATE值。支持的范围是'1000-01-01'
至'9999-12-31'
。
DATETIME
类型用于包含日期和时间部分的值。 MySQL在'YYYY-MM-DD HH:MM:SS'
格式中检索并显示DATETIME
值。支持的范围是'1000-01-01 00:00:00'
至'9999-12-31 23:59:59'
。
TIMESTAMP
数据类型用于包含日期和时间部分的值。TIMESTAMP
的范围为'1970-01-01 00:00:01'
UTC至'2038-01-19 03:14:07'
UTC。
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....
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 itsaltFormat
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 thedateFormat
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]";
Warning
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
orTIMESTAMP
column for holding a date value; I recommend you consider using MySQL'sDATE
type instead. As explained in TheDATE
,DATETIME
, andTIMESTAMP
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.
这篇关于PHP mysql插入日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!