How Do I Convert a PHP Date String to a MySQL-Compatible Format?

Convert PHP Date to MySQL Format

Converting PHP date fields to MySQL's required format can be achieved using specific functions and syntax. The original code provided:

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

requires conversion to ensure compatibility with MySQL. To do so, there are two options depending on the column type in MySQL:

For DATE Columns:

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

For DATETIME Columns:

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

The strtotime() function requires a specific format with forward slashes (/) as separators. Therefore, if the input date uses dashes (-) as separators, you need to replace them before using strtotime().

In the provided example, $date has the format dd/mm/yyyy hh:mm:ss. However, strtotime() cannot parse dates with dashes. So, you need to modify the code to:

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

This replaces dashes with slashes and rearranges the datetime components to match the MySQL-compatible format. The final output will be 2009-07-02 00:07:00, which can be inserted into your MySQL database.

The above is the detailed content of How Do I Convert a PHP Date String to a MySQL-Compatible Format?. For more information, please follow other related articles on the PHP Chinese website!