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?
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.
Use strtotime function to convert the date to correct format:
$date = date('Y-m-d', strtotime($date));
$this->db->get_where('tvprogram', array('DATE(date)' => $date))->result_array();
Please convert first posted date in database format and use with Date function in your searching field like this.
$date = date('Y-m-d', strtotime($search));
$this->db->where('DATE(created)',$date);