Hiding news entries more than 3 months old

NetRoam

Hi there!

The subject-line actually says it all ... I'd like to find some way of reducing my news-database so when people view the news on my website they will only see the latest news from today and 3 months back - meaning today it will show news from October 26th to now and tomorrow it will show news from October 27th to now.

Is there a way of doing that? And perhaps making another function where people are allowed to view the old "archived" news?

Thanks in advance!

/// NetRoam

AstroTeg

You can do with the SQL you may be using already.

WHERE DATEDIFF(yourdatefield,NOW()) < 90

This is one way (DATEDIFF() returns the number of days between two dates).

You could also use MONTH() and DAY() (or DATE() depending on your version of MySQL). Although this would require a little more work to validate the day is valid to the month in question.

NetRoam

Isn't DATEDIFF an ASP-function? I can't get it working and I searched php.net and saw that a person posted something about PHP really lacking an equivalent to ASP's Datediff

Anyway - bottom line is it doesn't work on my screen

AstroTeg

You use that in your SQL... Since you didn't mention the database, I'm defaulting to you using MySQL.

Not to go too far off topic, but as being an ASP developer for several years, PHP has better date handling functions then ASP. For this solution, you'll get a ton better performance if you do your date testing from the SQL statement then from code (be it PHP, ASP, or whatever).

NetRoam

Well, I don't know anything about ASP as I only learned PHP, but I just read it off php.net

Anyway - I do have a database already connected as I try to make the SQL-statement, but you want it to be like this?

$query = "select * from news WHERE DATEDIFF(newsDate,NOW()) < 90 order by newsDate desc";
$result = mysql_query($query) or die("".mysql_errno()." Error: ".mysql_error());

Anyway - the above doesn't work as it gives me this error-message:

1064 Error: You have an error in your SQL syntax near '(newsDate,NOW()) < 90 order by newsDate desc' at line 1

Any help is appreciated

AstroTeg

Maybe instead of using NOW(), try CURDATE(). Its possible its complaining about the date format.

NetRoam

Naah - doesn't work either!

AstroTeg

Is newsDate a datetime or date field? Or is it a varchar or text field?

NetRoam

It's a datetime field - sorry - I probably should have told you at the first post 🙂

AstroTeg

Oops - datediff() works for MySQL v4.1.1 and up.

You might check out DATE_SUB(). Maybe subtract the 3 months from the current time and then grab all dates newer then this time.

WHERE newsDate > (DATE_SUB(NOW(), INTERVAL 3 MONTHS))

http://www.mysql.com/doc/en/Date_and_time_functions.html

NetRoam

Thank you very much - that worked out perfect ... although "INTERVAL 3 MONTHS" should be "INTERVAL 3 MONTH" - but it worked out great!

Thanx again!