Curdate() last week, last month, etc

[SChaput]

I am trying to query my database for information that was entered along with the CURDATE() attribute, today, this week, this month.

if($type == "bestweek"){
$sql = "SELECT id, entrytext, positive, negative, verified, date, realtime FROM hmr WHERE verified > 2 AND DATE(realtime) = CURDATE()-7 ORDER BY positive DESC LIMIT $start, $limit";
$result = mysql_query($sql);

}


SHould the above code be giving me all results for the last week? When i run it it returns 0 results.
However, when i run this:

if($type == "besttoday"){
$sql = "SELECT id, entrytext, positive, negative, verified, date, realtime FROM hmr WHERE verified > 2 AND DATE(realtime) = CURDATE() ORDER BY positive DESC LIMIT $start, $limit"; $result = mysql_query($sql);


}

It gives me anything that was added today.

ANy help is appreciated.

[angelwatt]

The format of the date given back by CURDATE depends on how you use it,

from http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate
mysql> SELECT CURDATE();
-> '2008-06-13'
mysql> SELECT CURDATE() + 0;
-> 20080613

Your query is also only attempting to look for entries that were exactly a week a ago, not everything within the last week.

$sql = "SELECT id, entrytext, positive, negative, verified, date, realtime
FROM hmr WHERE verified > 2 AND
TO_DAYS(CURDATE()) - TO_DAYS(DATE(realtime)) <= 7
ORDER BY positive DESC
LIMIT $start, $limit";
alternative (depends on column type for realtime),
WHERE realtime >= DATE_SUB(NOW(), INTERVAL 1 WEEK)

I can't test the above, but should at least be close to what you need.