In my code, I am trying to find items in an activities table that are within the last day. This query is not returning any results, are there any problems with it? Is there a better query?
$curday = time() - (24*3600);
$query = "SELECT * FROM activities WHERE userid = '$userid' AND 'timestamp' > '$curday'";
in Informix it would be (TODAY - 1) if the column is type DATE
There are two choices here, you can get and format the date through PHP or use SQL language to do it. I prefer to do it within the SQL, it also allows me to use the same query in a MySQL client.
This question is essentially the same thing: MySQL SELECT last few days?
This would be the new query: $query = "SELECT * FROM activities WHERE userid = '$userid' AND 'timestamp' > DATE_ADD(CURDATE(), INTERVAL -1 DAY)";
What you can use is DATE_SUB. This can be used as follows
SELECT * FROM activities
WHERE userid = '$userid'
AND timestamp > date_sub(current_date, interval 1 day)
This way you don't need to work with current date in PHP
you can try with unix function 'mktime' to get value of yesterday .. as
$curday = mktime(0,0,0,date("m"),date("d")-1,date("Y"));
if your database will mysql only then you can extract yesterday in sql itself..
SELECT * FROM activities
WHERE userid = '$userid'
AND timestamp > DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)
one more thing if timestamp is your column name don't put this column inside single quote ..