PHP / MySQL - 以正确的格式准备字符串以插入日期时间SQL列

Question

In my UI the dates are shown like this - dd.mm.YYYY hh:ii:ss. The users are able to edit/add new dates and most probably they'll try to use the same format (24.06.2012 15:35:00) which can not be used for a SQL query. Here is what I've done till now:

$dt = (date_parse_from_format("d.m.Y H:i:s", $data['event_time']));
        $newdate = sprintf("%02d-%02d-%04d %02d:%02d:%02d" , $dt['day'], $dt['month'], $dt['year'], $dt['hour'], $dt['minute'], $dt['second']);
        $test = date("Y-m-d H:i:s", strtotime($newdate));
        if ($test == "1970-01-01 01:00:00")
        {
            throw new Exception('Invalid date');
        }

What happens is - if I leave the check if ($test == "1970-01-01 01:00:00") I get an exception, but if I comment the $test = date("Y-m-d H:i:s", strtotime($newdate)); line and the check the date is inserted only with zeros. $newdate is a sting in the right format for SQL - YYYY-mm-dd H:i:s but obv. I miss something here. How to insert this string as a valid SQL datetime?

Thanks

Leron

Answer 1

The DateTime class was introduced in PHP 5.2 and would allow you to use something like this

$dt = DateTime::createFromFormat("d.m.Y H:i:s", $data['event_time']);
if($dt === false){
  throw new Exception("Invalid date");
}

DateTime::createFromFormat returns false on failure (This method is only available since PHP 5.3)

Then when saving to the database you can use the following to get the correct format for MySQL

$dt->format("Y-m-d H:i:s")