Get MySQL Results from Current Time to Last Week

[scottnicol]

Hi,

 

I am trying to get the latest results from the last 7 days using MySQL. I have formatted the current time like so, and also made a variable the current timestamp, minus 7 days using mktime();.

 

$today = date("Y\-m\-d H:i:s");	
$lastweek = mktime(date("H"), date("i"), date("s"), date("m"), date("d")-7, date("Y"));
$lastweek = gmdate("Y-m-d H:i:s", $lastweek);

 

I use gmdate to convert Unix to normal timestamp, and I am in GMT, so it helps.

 

The query is:

$sqlquery = "SELECT * FROM dirt WHERE timestamp BETWEEN $today AND $lastweek ORDER BY votes DESC LIMIT 30";

 

The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15:52:44 AND 2010-07-11 15:52:44 ORDER BY votes DESC LIMIT 30' at line 1

 

Anyone know what is wrong and how do I get it to find the last results from the past week? I think it works fine up until the query, but I'm not sure why. Column 'timestamp' does exist.

 

Thanks.

 

[Mchl]

SELECT * FROM dirt WHERE timestamp BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() ORDER BY votes DESC LIMIT 30

[PFMaBiSmAd]

Literal datetime values are strings and must be enclosed in single-quotes.

 

Assuming you have set the timezone in mysql, the following query will (untested) accomplish the same thing -

$sqlquery = "SELECT * FROM dirt WHERE timestamp BETWEEN NOW() - INTERVAL 7 DAY AND NOW() ORDER BY votes DESC LIMIT 30";

 

Also the BETWEEN min AND max syntax need the min to be less than max. Your query had min > max because today is greater than a date in the past.

[scottnicol]

Thanks ever so much, that seemed to make it work!