PHP mysql 插入日期格式 [英] PHP mysql insert date format

问题描述

我正在使用 jQuery 日期选择器日期选择器的格式是这个 08/25/2012

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

我在插入到我的数据库时出错，它只插入了 0000-00-00 00 00 00

i have errors when inserting to my database it insert only 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';
?>

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

im sure my insert is correct....

推荐答案

如 日期和时间文字:

MySQL 识别这些格式的 DATE 值:

MySQL recognizes DATE values in these formats:

• 作为 'YYYY-MM-DD' 或 'YY-MM-DD' 格式的字符串.允许使用宽松"语法:任何标点字符都可以用作日期部分之间的分隔符.例如，'2012-12-31'、'2012/12/31'、'2012^12^31'和'2012@12@31' 是等价的.

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

作为 'YYYYMMDD' 或 'YYMMDD' 格式的没有分隔符的字符串，前提是该字符串作为日期有意义.例如，'20070523' 和 '070523' 被解释为 '2007-05-23'，但 '071332' 是非法的(它有无意义的月份和日期部分)并变成 '0000-00-00'.

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

作为 YYYYMMDD 或 YYMMDD 格式的数字，前提是该数字作为日期有意义.例如，19830905 和 830905 被解释为 '1983-09-05'.

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 日期文字.您有四个选项(按照一些模糊的偏好顺序，没有关于您的要求的任何进一步信息):

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. 使用 altField<配置 Datepicker 以提供受支持格式的日期/code> 连同它的 altFormat 选项:

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"
});

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

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"
});

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

Use MySQL's STR_TO_DATE() function to convert the string:

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

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

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

然后:

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

• obtain a suitable formatted string:

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

获取 UNIX 时间戳:

obtain the UNIX timestamp:

$timestamp = $dt->getTimestamp();

然后直接传递给 MySQL 的 <代码>FROM_UNIXTIME() 函数:

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

<小时>

警告

1. 您的代码容易受到 SQL 注入攻击.您真的应该使用 准备好的语句，您将变量作为参数传递到其中，这些参数不会为 SQL 求值.如果你不知道我在说什么，或者如何解决它，请阅读 鲍比桌.

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.

此外，如介绍中所述mysql_* 函数的 PHP 手册章节:

Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions:

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

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.

您似乎在使用 DATETIME 或 TIMESTAMP 列来保存日期值；我建议您考虑使用 MySQL 的 DATE 类型代替.如DATE、DATETIME 中所述, 和 TIMESTAMP 类型:

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:

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

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

DATETIME 类型用于包含日期和时间部分的值.MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索和显示 DATETIME 值.支持的范围是 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'.

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

TIMESTAMP 数据类型用于包含日期和时间部分的值.TIMESTAMP 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC.

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屋！