PHP mysql 插入日期格式

Question

我正在使用 jQuery 日期选择器日期选择器的格式是这样的 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')" ) or die ( mysql_error() );
echo 'insert successful';
?>

我确信我的插入是正确的。

Answer 1

如 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'.

因此，字符串'08/25/2012'不是有效的 MySQL 日期文字。您有四个选项(按照一些模糊的偏好顺序，没有任何有关您的要求的进一步信息):

1. 使用altField配置日期选择器以支持的格式提供日期。连同其 altFormat option :

   <input type="hidden" id="actualDate" name="actualDate"/>
   

   $( "selector" ).datepicker({
       altField : "#actualDate"
       altFormat: "yyyy-mm-dd"
   });
   

   或者，如果您希望用户看到 YYYY-MM-DD 格式的日期，只需设置 dateFormat option相反:

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

2. 使用 MySQL 的 STR_TO_DATE()转换字符串的函数:

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

3. 将从 jQuery 接收到的字符串转换为 PHP 可以理解的日期，例如 DateTime对象:

   $dt = \DateTime::createFromFormat('m/d/Y', $_POST['date']);
   

   然后:

   • 获取合适的格式化字符串:

     $date = $dt->format('Y-m-d');
     

   • 获取 UNIX 时间戳:

     $timestamp = $dt->getTimestamp();
     

     然后直接传递到MySQL的FROM_UNIXTIME()功能:

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

4. 手动将字符串操作为有效的文字:

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

<小时/>

警告

1. 您的代码容易受到 SQL 注入(inject)的攻击。<​​/strong>您确实应该使用 prepared statements ，您将变量作为参数传递到其中，这些变量不会针对 SQL 进行评估。如果您不知道我在说什么或如何解决它，请阅读 Bobby Tables 的故事。

2. 此外，如 the introduction 中所述PHP 手册中有关 mysql_* 函数的章节:

   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.

3. 您似乎使用 DATETIME 或 TIMESTAMP 列来保存日期值；我建议您考虑使用 MySQL 的 DATE 类型。如 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.