PHP mysql 插入日期格式

PHP mysql insert date format

提问by Butternut

Im using jQuery datepicker the format of the datepicker is this 08/25/2012

我使用 jQuery datepicker 日期选择器的格式是这样的 08/25/2012

i have errors when inserting to my database it insert only 0000-00-00 00 00 00

插入到我的数据库时出错，它只插入 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....

我确定我的插入是正确的....

回答by eggyal

As stated in Date and Time Literals:

如日期和时间文字所述：

MySQL recognizes DATEvalues 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 YYYYMMDDor YYMMDDformat, provided that the number makes sense as a date. For example, 19830905and 830905are interpreted as '1983-09-05'.

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'。

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

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

1. Configure Datepicker to provide dates in a supported format using an altFieldtogether with its altFormatoption:

   <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-DDformat, simply set the dateFormatoptioninstead:

   $( "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 DateTimeobject:

   $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]";
   

1. 将 Datepicker 配置为使用altField及其altFormat选项以受支持的格式提供日期：

   <input type="hidden" id="actualDate" name="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对象：

   $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]";
   

---

---

Warning

警告

1. Your code is vulnerable to SQL injection.You reallyshould 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 introductionto 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 mysqlior PDO_MySQLextension should be used. See also the MySQL API Overviewfor further help while choosing a MySQL API.

3. You appear to be using either a DATETIMEor TIMESTAMPcolumn for holding a date value; I recommend you consider using MySQL's DATEtype instead. As explained in The DATE, DATETIME, and TIMESTAMPTypes:

   The DATEtype 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 DATETIMEtype is used for values that contain both date and time parts. MySQL retrieves and displays DATETIMEvalues 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 TIMESTAMPdata type is used for values that contain both date and time parts. TIMESTAMPhas a range of '1970-01-01 00:00:01'UTC to '2038-01-19 03:14:07'UTC.

1. 您的代码容易受到 SQL 注入的影响。您确实应该使用准备好的语句，您将变量作为参数传递到其中，而不会为 SQL 求值。如果您不知道我在说什么或如何解决它，请阅读Bobby Tables的故事。

2. 此外，如PHP 手册章节中关于函数的介绍中所述mysql_*：

   自 PHP 5.5.0 起，此扩展已弃用，不推荐用于编写新代码，因为将来会删除它。相反，应该使用mysqli或PDO_MySQL扩展。在选择 MySQL API 时，另请参阅MySQL API 概述以获取进一步帮助。

3. 您似乎使用 aDATETIME或TIMESTAMP列来保存日期值；我建议您考虑使用 MySQL 的DATE类型。正如上文中DATE，DATETIME和TIMESTAMP类型：

   该DATE类型用于具有日期部分但没有时间部分的值。MySQL 以'YYYY-MM-DD'格式检索和显示 DATE 值。支持的范围是'1000-01-01'到'9999-12-31'。

   该DATETIME类型用于同时包含日期和时间部分的值。MySQLDATETIME以'YYYY-MM-DD HH:MM:SS'格式检索和显示值。支持的范围是'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的范围。

回答by Mihai Iorga

You should consider creating a timestamp from that date witk mktime()

您应该考虑从那个日期创建一个时间戳 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 );

回答by Kishan Chauhan

$date_field         = date('Y-m-d',strtotime($_POST['date_field']));
$sql = mysql_query("INSERT INTO user_date (column_name,column_name,column_name) VALUES('',$name,$date_field)") or die (mysql_error());

回答by Ilia Rostovtsev

You must make sure that the date format is YYYY-MM-DD on your jQuery output. I can see jQuery returns MM-DD-YYYY, which is not the valid MySQL date formatand this is why it returns an error.

您必须确保 jQuery 输出的日期格式为 YYYY-MM-DD。我可以看到 jQuery 返回 MM-DD-YYYY，这不是有效的MySQL 日期格式，这就是它返回错误的原因。

To convert it to the right one you could do this:

要将其转换为正确的，您可以执行以下操作：

$dateFormated = split('/', $date);
$date = $dateFormated[2].'-'.$dateFormated[0].'-'.$dateFormated[1];

Then you will get formatted date that will be valid MySQL format, which is YYYY-MM-DD, i.e. 2012-08-25

然后您将获得格式化的日期，该日期将是有效的 MySQL 格式，即 YYYY-MM-DD，即2012-08-25

I would also recommend using mysql_real_escape_stringas you insert data into database to prevent SQL injectionsas a quick solution or better use PDOor MySQLi.

我还建议在将数据插入数据库时使用mysql_real_escape_string以防止SQL 注入作为快速解决方案或更好地使用PDO或MySQLi。

Your insert query using mysql_real_escape_stringshould rather look like this:

您使用的插入查询mysql_real_escape_string应该是这样的：

$sql = mysql_query( "INSERT INTO user_date VALUE( '', '" .mysql_real_escape_string($name). "', '" .mysql_real_escape_string($date). "'" ) or die ( mysql_error() );

回答by ykay says Reinstate Monica

Get a date object from the jquery date picker using

使用 jquery 日期选择器获取日期对象

var myDate = $('element').datepicker('getDate')

For mysql the date needs to be in the proper format. One option which handles any timezone issues is to use moment.js

对于 mysql，日期需要采用正确的格式。处理任何时区问题的一种选择是使用 moment.js

moment(myDate).format('YYYY-MM-DD HH:mm:ss')

回答by William Worley

The simplest method is

最简单的方法是

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

$sql = mysql_query("INSERT INTO user_date (column,column,column) VALUES('',$name,$date)") or die (mysql_error());

回答by Pupan Poulkaew

HTML:

HTML：

<div class="form-group">
  <label for="pt_date" class="col-2 col-form-label">Date</label>
  <input class="form-control" type="date" value=<?php echo  date("Y-m-d") ;?> id="pt_date" name="pt_date">
</div>

SQL

SQL

$pt_date = $_POST['pt_date'];

$sql = "INSERT INTO `table` ( `pt_date`) VALUES ( '$pt_date')";

回答by Anand Raj Mehta

First of all store $date=$_POST['your date field name'];

首先店铺 $date=$_POST['your date field name'];

insert into **Your_Table Name** values('$date',**other fields**);

You must contain date in single cote (' ')

您必须在单个 cote (' ') 中包含日期

I hope it is helps.

我希望它有帮助。

回答by shubham prajapati

Try Something like this..

尝试这样的事情..

echo "The time is " . date("2:50:20");
$d=strtotime("3.00pm july 28 2014");
echo "Created date is " . date("d-m-y h:i:sa",$d);