display result of last 30 days

[php_begins]

I have a post table that has some of these fields:

i want to query the number of posts that happened in the last 30 days and display the date.i.e display posts that happened on each date and the also display the date.

EG:

            NO  DATE

POSTS 10  1/9/2011

POSTS  20  31/8/2011

 

i know i can do a select postid and then do a mysql_num_rows to get the number of posts. But I am not sure how to get the result of last 30 days and display that date. Here is my table structure

postid  username userid   title             dateline

1            test                3        member          1186945212

3            tester              5          JM                  1187106827

[micah1701]

something like...

 

<?php

$thirty_days_ago = strtotime("-30 days");

$sql = "SELECT * FROM `table_name` WHERE dateline >= $thirty_days_ago

?>

[php_begins]

so if I need to display the last 30 dates one by one, i would just need to do mysql_fetch_assoc in a while loop and display it right?

[micah1701]

exactly.

 

also, i should point out a tiny flaw in my code.  since you're using a unix timestamp, the "30 days ago" is based on 30 days before the current second of today.  so things that happened on the day 30 days ago but before the current time won't be found.

 

so really, instead of:

 

strtotime("-30 days"); // which is 30 days before the current second of the current day

 

you should use:

 

strtotime("-30 days", strtotime(date("Y-m-d")) );  // which is 30 days before this morning at midnight

 

hope that helps!

 

FYI, next time it might be better to just use the "date" column type in MySQL in the first place which formats all your deads in human readable "YYYY-MM-DD" format and allows mysql to do some pretty easy calculations as well.

[fenway]

Clearly, you're not searching the forums -- http://www.phpfreaks.com/forums/index.php?topic=342584.0.