javascript 将 jQuery 日期转换为 php 日期或 mysql 日期以正确插入到数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12202624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert jQuery date to php date or mysql date to be inserted correctly in database
提问by Snake Eyes
I use jQuery datepicker with the following code
我使用带有以下代码的 jQuery datepicker
$(".item").datepicker({
showWeek: true,
firstDay: 1,
minDate: -30,
dateFormat: 'mm-dd-yy'
});
The value of that datepicker will be sent via ajax to php function which insert a data in mysql table.
该日期选择器的值将通过 ajax 发送到 php 函数，该函数在 mysql 表中插入数据。
The datecolumn type from mysql database is Datetime.
datemysql 数据库中的列类型是Datetime.
Every time when read value from datepicker input value, the datecolumn in database is empty shows 00-00-0000 00:00:00.
每次从 datepicker 输入值读取值时date，数据库中的列为空显示00-00-0000 00:00:00.
I am newbie in php and maybe I made somewhere a mistake.
我是 php 新手，也许我在某个地方犯了错误。
the php piece of code
php 代码段
mysqli_query($connect, "Insert into tab(date) values ('".$myData."')");
mysqli_query($connect, "Insert into tab(date) values ('".$myData."')");
how to format javascript date in that right for mysql ?
如何为 mysql 格式化 javascript 日期？
回答by Moe Tsao
Can you first verify that the datepicker is posting a correct value to the server?
您能否先验证日期选择器是否向服务器发布了正确的值？
Try alert the value somewhere.
尝试在某处提醒值。
If you have the correct input from the javascript, the php part of the script can be done like this:
如果您从 javascript 中获得了正确的输入，则脚本的 php 部分可以这样完成：
if (isset$_GET['date']){$date=$_GET['date'];}
$date=date("Y-m-d h:i:s",strtotime($date));
Echo out to confirm you have it right, and finally insert that $date into the table.
回显以确认您做对了，最后将 $date 插入表中。
回答by jeroen
The date format in mysql is YYYY-MM-DD so you could use strtotime:
mysql 中的日期格式是 YYYY-MM-DD 所以你可以使用strtotime：
$myDataSQL = date("Y-m-d", strtotime($myData));
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");
$myDataSQL = date("Y-m-d", strtotime($myData));
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");
By the way, I would recommend using prepared statements to avoid sql injection problems although it does not really matter much in this specific case. I always use prepared statements so I don't have to think about it.
顺便说一句，我建议使用准备好的语句来避免 sql 注入问题，尽管在这种特定情况下它并不重要。我总是使用准备好的语句，所以我不必考虑它。
Edit:It seems strtotime needs /separatorsfor that to work.
编辑：似乎strtotime 需要/分隔符才能工作。
If you are on PHP 5.3+ you can use the DateTimeclass:
如果您使用的是 PHP 5.3+，则可以使用DateTime该类：
$date = DateTime::createFromFormat('m-d-y', $myData);
$myDataSQL = $date->format('Y-m-d');
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");
回答by Sednus
queryAccording to mysql documentation:
查询根据 mysql文档：
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.
作为 'YYYY-MM-DD' 或 'YY-MM-DD' 格式的字符串。允许使用“宽松”语法：任何标点字符都可以用作日期部分之间的分隔符。例如，“2012-12-31”、“2012/12/31”、“2012^12^31”和“2012@12@31”是等价的。
so you could just change the format in which datepicker is taking the date.
所以你可以改变日期选择器获取日期的格式。
$( ".item" ).datepicker({ dateFormat: "yy-mm-dd" });
putting that into your update should do the trick and you wont even need additional server operations for parsing. Just make sure it will reach the server safely.
把它放到你的更新中应该可以解决问题，你甚至不需要额外的服务器操作来解析。只要确保它会安全地到达服务器。
回答by Mike
A different take on jeroen's answer which is just as suitable
对 jeroen 的回答的不同看法同样合适
$myDataSQL = date("c", strtotime($myData));
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");
I find using the ISO 8601 date everywhere easier to handle timezones as they are visible. Plus it inserts into mysql.
我发现在任何地方使用 ISO 8601 日期更容易处理时区，因为它们是可见的。另外它插入到mysql中。
Also in the past I have had issues with jQuery datapicker in the needing to specify a better format in its initialisation.
同样在过去，我在使用 jQuery datapicker 时遇到了问题，需要在其初始化中指定更好的格式。
Maybe try
也许试试
$(".item").datepicker({
showWeek: true,
firstDay: 1,
minDate: -30,
dateFormat: "yy/mm/dd"
});
回答by ling
I use a convertor (source code here: https://github.com/lingtalfi/DatePickerHelper/blob/master/DatePickerHelper.php)
我使用转换器（源代码在这里：https: //github.com/lingtalfi/DatePickerHelper/blob/master/DatePickerHelper.php）
Usage:
用法：
<?php
echo DatePickerHelper::convertFromDatePickerToPhpDate("dd/mm/yy"); // prints d/m/Y
echo DatePickerHelper::convertFromPhpDateToDatePicker("Y-m-d"); // prints yy-mm-dd
$birthdayDate = "09/12/1944"; // your input from the web
echo DatePickerHelper::convertFromNumericInputToMysqlDate($birthdayDate, "d/m/Y"); // 1944-12-09
-
——
class DatePickerHelper
{
private static $map = [
"yy" => "<1>",
"y" => "<2>",
"MM" => "<3>",
"M" => "<4>",
"mm" => "<5>",
"m" => "<6>",
"DD" => "<7>",
"D" => "<8>",
"oo" => "<9>",
"o" => "<10>",
"dd" => "<11>",
"d" => "<12>",
];
private static $map2 = [
"<1>" => 'Y',
"<2>" => 'y',
"<3>" => 'F',
"<4>" => 'M',
"<5>" => 'm',
"<6>" => 'n',
"<7>" => 'l',
"<8>" => 'D',
"<9>" => 'z', // note: php doesn't have "day of the year with three digits", but this is the closest
"<10>" => 'z',
"<11>" => 'd',
"<12>" => 'j',
];
private static $mapRegex = [
'Y' => '<1>',
'y' => '<2>',
'm' => '<3>',
'n' => '<4>',
'd' => '<5>',
'j' => '<6>',
];
private static $mapRegex2 = [
'<1>' => '(?P<year4>[0-9]{4})',
'<2>' => '(?P<year2>[0-9]{2})',
'<3>' => '(?P<month_leading>[0-9]{2})',
'<4>' => '(?P<month_no_leading>[0-9]{1,2})',
'<5>' => '(?P<day_leading>[0-9]{2})',
'<6>' => '(?P<day_no_leading>[0-9]{1,2})',
];
public static function convertFromDatePickerToPhpDate(string $datePickerFormat): string
{
$map = self::$map;
$map2 = self::$map2;
$first = str_replace(array_keys($map), array_values($map), $datePickerFormat);
return str_replace(array_keys($map2), array_values($map2), $first);
}
public static function convertFromPhpDateToDatePicker(string $phpDate): string
{
$map2 = array_flip(self::$map2);
$map = array_flip(self::$map);
$first = str_replace(array_keys($map2), array_values($map2), $phpDate);
return str_replace(array_keys($map), array_values($map), $first);
}
/**
* @param string $input , the string to convert, the format of this string should match the given phpFormat
* Plus, it must contain exactly:
* - one day component
* - one month component
* - one year component
*
* @param string $phpFormat , all components of the phpFormat have to be one of those:
* - Y: year, four digits
* - y: year, two digits
* - m: numeric month, with leading zeros
* - n: numeric month, without leading zeros
* - d: numeric day of the month, with leading zeros
* - j: numeric day of the month, without leading zeros
*/
public static function convertFromNumericInputToMysqlDate(string $input, string $phpFormat)
{
$map = self::$mapRegex;
$map2 = self::$mapRegex2;
$first = str_replace(array_keys($map), array_values($map), $phpFormat);
$pattern = str_replace(array_keys($map2), array_values($map2), $first);
if (preg_match('!' . $pattern . '!', $input, $match)) {
$day = $match['day_leading'] ?? $match['day_no_leading'] ?? null;
if (null !== $day) {
$day = (int)$day;
$month = $match['month_leading'] ?? $match['month_no_leading'] ?? null;
if (null !== $month) {
if (
array_key_exists("year4", $match) ||
array_key_exists("year2", $match)
) {
// a component of each type is there, we will be able to return a result
if (array_key_exists("year4", $match)) {
$year = (int)$match['year4'];
} else {
// assumed it's 20, but we don't know really, that sucks.
// That's why you should use year4 instead...
$year = "20" . $match['year2'];
$year = (int)$year;
}
return $year . "-" . sprintf('%02s', $month) . "-" . sprintf("%02s", $day);
}
}
}
}
return false;
}
}
回答by Maxim Roman
As in JavaScript date values are created using let date = new Date("2017-01-26");, you have to use the same format when formatting the date in your PHP script.
在 JavaScript 中，日期值是使用 来创建的let date = new Date("2017-01-26");，因此在 PHP 脚本中格式化日期时必须使用相同的格式。
Just convert yout php date as following format date('Y-m-d', $date).
只需将您的 php 日期转换为以下格式date('Y-m-d', $date)。
This will give you the right format for javascript <input type="date">field.
这将为您提供 javascript<input type="date">字段的正确格式。
