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.