How to Convert PHP Dates to MySQL DATE and DATETIME Formats?

Converting PHP Date to MySQL Format

In PHP, it is common to encounter date fields in the format used in your code:

$date = mysql_real_escape_string($_POST['intake_date']);

However, when working with MySQL, it is often necessary to convert this date into the MySQL format of "0000-00-00" for database inclusion. While some variations of date('Y-m-d' strtotime($date);) may come to mind, it's crucial to address specific intricacies involved in this conversion.

1. Handling MySQL Column Type

If your MySQL column is of the DATE type, indicating only a date without time, the recommended conversion method is:

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

This replaces the dashes "-" in the date string with forward slashes "/" before applying the strtotime() function. Otherwise, strtotime() will attempt to subtract the numbers separated by the dashes, leading to incorrect results.

2. Conversion for DATETIME Type

If your MySQL column is of the DATETIME type, which includes both date and time components, use the following conversion instead:

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

Here, we adhere to the same principle of replacing "-" with "/", but we also include additional formatting for time components.

3. Alternative Approach for Complex Formats

In case your date is in a format that is incompatible with both strtotime() and date() functions, such as "02/07/2009 00:07:00", you can opt for a regular expression-based conversion:

$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '-- ', $date);

This regex rearranges the date components into the required MySQL format, ensuring that your PHP date field is appropriately converted for inclusion in the database.

The above is the detailed content of How to Convert PHP Dates to MySQL DATE and DATETIME Formats?. For more information, please follow other related articles on the PHP Chinese website!