How to Properly Insert Dates from PHP into MySQL?

How to Insert Dates in MySQL Using PHP

Inserting dates into MySQL can be a confusing task, especially when using different date formats. This article will demonstrate how to solve the common issue of inserting dates using PHP and MySQL, focusing on the "PHP mysql insert date format" problem.

The Problem

When using the jQuery datepicker, the date format is typically in the form of "MM/DD/YYYY" (e.g., "08/25/2012"). However, MySQL requires date values to be in one of the following formats:

• 'YYYY-MM-DD'
• 'YY-MM-DD'
• YYYYMMDD
• YYMMDD

Attempting to insert a date in the "MM/DD/YYYY" format will result in an error and insertion of '0000-00-00 00:00:00'.

Solution

There are several options to resolve this issue:

1. Configure Datepicker: Set the altFormat or dateFormat options to 'YYYY-MM-DD' to ensure Datepicker provides dates in the correct format.
2. Use STR_TO_DATE(): Convert the date string to MySQL format using the STR_TO_DATE() function:

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

3. Convert to PHP DateTime: Create a PHP DateTime object and format the date or retrieve the timestamp:

$dt = DateTime::createFromFormat('m/d/Y', $_POST['date']);
$date = $dt->format('Y-m-d'); // formatted string
$timestamp = $dt->getTimestamp(); // UNIX timestamp for FROM_UNIXTIME() function

4. Manually Manipulate String: Break the date string into parts and concatenate it into the required format:

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

Important Notes

• Use prepared statements to prevent SQL injection.
• The mysql_* functions are deprecated. Consider using mysqli or PDO_MySQL instead.
• Consider using the DATE type instead of DATETIME or TIMESTAMP for date storage.

By implementing one of these solutions, you can successfully insert dates in the correct format into MySQL.

The above is the detailed content of How to Properly Insert Dates from PHP into MySQL?. For more information, please follow other related articles on the PHP Chinese website!