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日期文字。您有四个选项（在某些模糊的优先顺序中，不需要任何进一步信息）：

1. 配置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
   }）; 
     




2. 使用MySQL的 STR_TO_DATE（） 函数转换字符串：

     INSERT INTO user_date VALUES（''，'$ name'，STR_TO_DATE（'$ date'，'％m /％d /％Y'））
     




3. 将从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））
       

   
   



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

     $ parts = explode（'/'，$ _POST ['date']）; 
    $ date =$ parts [2]  -  $ parts [0]  -  $ parts [1]; 
     





---

< h2>警告

1. 您的代码容易受到SQL注入的攻击。<​​/ strong> em>应该使用准备好的语句，您将变量作为不被SQL评估的参数传递到该语句中。如果你不知道我在说什么，或者如何解决它，请阅读 Bobby Tables 。




2. 同样，如介绍到PHP手册章节的 mysql _ * 函数：

   
   

   这个扩展名从PHP 5.5.0开始已被弃用，不建议用于编写新的代码，因为它将来会被删除。相反， mysqli 或PDO_MySQL 扩展名。另请参见 MySQL API概述，以便在选择MySQL API时进一步获得帮助。

   
   




3. 您似乎正在使用 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 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 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"
   });
   

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

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

这篇关于PHP mysql插入日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！