Curdate() last week, last month, etc

Discussion in 'Web Design and Development' started by SChaput, Nov 3, 2009.

  1. SChaput macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #1
    I am trying to query my database for information that was entered along with the CURDATE() attribute, today, this week, this month.
    Code:
    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:
    Code:
    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.
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    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
    Code:
    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.

    PHP:
    $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),
    Code:
    WHERE realtime >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
    I can't test the above, but should at least be close to what you need.
     

Share This Page