Mysql extraction method?

[Anon]

Hi i have a "news" script that pulls news out of my mysql database table called news. the field in the table are as follows: news_id, news_title, news_posted_on, news_posted_by, and news_body. Here is my problem: I am currently using the code below, but that code just lists it. How would i go about listing the news for 2 months only and in a backwards (as far as the database is conserned) order? Here's the code i have now:
$result = mysql_query("SELECT * FROM news",$db);
echo "<table width=100% border=0 cellspacing=0 cellpadding=0>\n";

while ($myrow = mysql_fetch_row($result)) {

printf("<tr bgcolor=#99CCFF>\n
<td><font face=Courier New, Courier, mono color=#000000>$myrow[1]</font></td>\n
<td> \n
<div align=right><font face=Courier New, Courier, mono color=#000000>Posted On: $myrow[2] </font> </div>\n
</td>\n
</tr>\n
<tr bgcolor=#CCCCCC> \n
<td><font face=Courier New, Courier, mono>Posted By: $myrow[3]</font></td>\n
<td>&nbsp;</td>\n
</tr>\n
<tr>\n
<td colspan=2 bgcolor=#FFD1A4><font face=Courier New, Courier, mono>$myrow[4]</font></td>\n
</tr>\n
<tr> \n
<td colspan=2 height=10></td>\n");
}
echo "</tr>\n
</table>\n";

Thanks

[reagent]

try this query:
select
news_id,
news_title,
news_posted_on,
news_posted_by,
news_body
from news
where news_posted_on < DATE_SUB(now(), INTERVAL 2 MONTH)
order by news_posted_on DESC

that should do it (i'm assuming you want the latest news items displayed first)!

[tsmith]

First figure out what today is. Use:
$today = getdate();
Or mysql's timestamp() might work better.
try:
mysql > select unix_timestamp();
from the command line, then get that minus 60 days.

Change your query a bit. Make it like:

$sql = "select * from news where news_posted_on >= ($today - 60) order by news_id desc ";
Ordering in descending order will show newest on top, I think.

Then do:
$result = mysql_query($sql,$db);
echo "<table width=100% border=0 cellspacing=0 cellpadding=0>\n";

There, that was absolutely no help! 🙂
t

[reagent]

correction:
that should be

select
news_id,
news_title,
news_posted_on,
news_posted_by,
news_body
from news
where news_posted_on >= DATE_SUB(now(), INTERVAL 2 MONTH)
order by news_posted_on DESC

sorry for the confusion!

[tsmith]

PR, Nice query!

[Anon]

Thanks, that did it!