I have a php function which is retrieving a list of records based on a 'created_at' column.
public function browseResults($days = 90) {
$search_period_in_seconds = 60*60*24*$days;
$today = time();
$reference_date = $today - $search_period_in_seconds;
$query ="SELECT * FROM brands";
$query .=" WHERE created_at > ".$reference_date;
$results = DB::select($query); // This is laravel syntax for performing the query.
This isnt returning the required results. I think the issue is because the MySql database is storing the dates in the following format: 2013-08-04 15:54:42 which is then compared to a unix timestamp. I have tried inserting some form of strtotime() function into the SQL but this caused an error. Is there anyway of avoiding having to pull all of the records from the database and doing the comparison in PHP? i.e. is it possible to perform the filter in the SQL query?
Many thanks
You could check out the MySQL function FROM_UNIXTIME:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
Or you could create the correct date with PHP:
$datetime = new DateTime("-" . $days . " days"); // minus $days days
$date_sql = $datetime->format("Y-m-d H:i:s"); // Format the date as a string ex. 2013-08-07 00:00:00
$query ="SELECT * FROM brands";
$query .=" WHERE created_at > '$date_sql'";
// Etc.