Manipulating timestamp to sort by time interval.

[newbtophp]

Hi, I'm stuck, im my table im logging the date (using time()), theirfore contains the column, date (which contains a timestamp).

 

However I'd like to know how to display content from that table based on this week (weekly)?, and another based on today (daily)?. Im thinking I'd need to perhaps manipulate the date column.

 

Heres my code so you can get an understanding of my structure:

 

<?php

//Weekly 10
$result = mysql_query("SELECT * FROM site_reviews ORDER BY date DESC LIMIT 10");

while ($row = mysql_fetch_array($result)){

echo $row['date']."<br>";

echo $row['review'];

}

//Daily 5
$result = mysql_query("SELECT * FROM site_reviews ORDER BY date DESC LIMIT 5");

while ($row = mysql_fetch_array($result)){

echo $row['date']."<br>";

echo $row['review'];

}

?>

 

 

Appreciate any help.  :-\

[andrewgauger]

You can do date comparisons in MySQL.

SELECT * FROM site_reviews ORDER BY date DESC LIMIT 10

Would need a WHERE clause as:

WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date

 

or for timestamps:

 

WHERE date > (NOW() - 604800)

 

And you would just divide the constants by 7 to get the appropriate daily.