Past 30 days articles

emdee

I'm currently writing a diary type of application that will have a lot of potential puposes when completed.

My current problem, however, is that I wish to display the past 30 day's postings.

I currently have a script and sql statement to select the articles in the current month but at the start of every month the page will be blank, as new.

What I am looking for is someway to do the following:

Get todays date (I'm using the date() function)
Calculate 30 prior to this date
Select all the records (efficiently) that fall between these dates.

I currently have this code:
#
$this_month = date("Y-m");
$sql = "SELECT * FROM data WHERE date like \"$this_month-__\" ";
#

But as I said this only gives me the articles in the current month and not 30 days prior to todays date like I want.

Thanks in advance
Mike.

amcgrath

Would "select * from data where date between now() and now() - 30" help you?

emdee

Unfortunately not.
The between function returns the value 1 or 0, I guess that's good for searches but doesn't give me the result I want.

Thanks for the suggestion though.
Mike

emdee

I was looking at another thread and found this:

SELECT * FROM table_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

There's more at:
http://www.mysql.com/doc/D/a/Date_and_time_functions.html

I'll write back if it doesn't work!

emdee

$sql = "SELECT * FROM data WHERE TO_DAYS(NOW()) - TO_DAYS(date) <= 30 ORDER BY date DESC, time DESC";

I've tested it thouroughly and it only shows the past 30 day's entries no more no less.

It'll be good for making a www.theregister.co.uk
type site with regular articles.