Codeigniter 2.1，MySQL - 按日期搜索

Question

In my DB I have date field with a standard datetime for MySQL (YYYY-MM-DD HH:MM:SS). Trough AJAX request I am getting this time: DD/MM/YYYY

This is the function I had written to search trough DB:

$this->db->get_where('tvprogram', array('DATE(date)' => $date))->result_array();

At the moment this function isn't getting any data. What seems to be wrong here? How can I search and find all entries for specific day?

Answer 1

Check the format coming out of DATE(date) because it is probably like YYYY-MM-DD and the comparison will always fail.

Go into phpMyAdmin or similar and try SELECT DATE(now()) and double check the date format.

If $date has a format like DD/MM/YYY, then you can reformat it by using:

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

From the PHP manual (http://www.php.net/manual/en/function.strtotime.php):

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

To fix the American/European date ambiguity, I simply replace "/" with "-" before the string to time conversion.