[SOLVED] Newer than one week, query not working...

[delphi123]

Any ideas what's wrong with this?

 

SELECT

jos_content.title,

jos_content.introtext,

jos_content.id,

jos_content.publish_up,

jos_content.hits,

jos_content.catid,

jos_content.sectionid,

jos_content.state

FROM

jos_content

WHERE

jos_content.publish_up + interval 7 day < now()

ORDER BY

jos_content.publish_up DESC

 

I'm trying to get articles published in the last week, but it's either returning everything or nothing depending which way I put the greater/less than in the WHERE statement (have tried both)...

[Mchl]

DATE_ADD(now(),interval -7 day) > jos_content.publish_up

[delphi123]

Hi Mchl,

 

Yeah had tried it with DATE_ADD originally, but it's still doing the same thing...

 

So I've now got:

SELECT

jos_content.title,

jos_content.introtext,

jos_content.id,

jos_content.publish_up,

jos_content.hits,

jos_content.catid,

jos_content.sectionid,

jos_content.state

FROM

jos_content

WHERE

DATE_ADD(now(),interval -7 day) > jos_content.publish_up

ORDER BY

jos_content.publish_up DESC

[PFMaBiSmAd]

You need to post the column definition and/or an example of what is in the column.

[delphi123]

I've also tried setting the date through php, but that's doing the same thing:

 

$query="SELECT * FROM jos_content WHERE publish_up <= '".date("Y-m-d",mktime(0,0,0,date("n"),date("j")-7,date("Y")))."' ORDER BY jos_content.publish_up DESC";

echo "<h3>This query returns:</h3>";

echo $query;

$result=mysql_query($query);

 

$num=mysql_numrows($result);

mysql_close();

 

echo "<h3>Query Returns:</h3><table>";

 

$i=0;

while ($i < $num) {

 

$title=mysql_result($result,$i,"title");

$publish_up=mysql_result($result,$i,"publish_up");

 

echo "<tr><td>$title</td> <td>$publish_up</td></tr>";

 

$i++;

}

 

Very strange...

 

Here's the first line of the two columns:

 

$title = Artbeats: royalty-free stock footage / varchar

$publish_up = 2009-10-14 18:12:56  /datetime

 

 

 

[delphi123]

oh damn, just realised I'm connected to the dev database, not the one I though I was - so there are no articles newer than a week!

 

Feel free to all hit the 'pound the idiot forum poster' button! (hey that'd be a great feature to these forums ;o) )

[Mchl]

Feel free to all hit the 'pound the idiot forum poster' button! (hey that'd be a great feature to these forums ;o) )

 

You can suggest it in PHPFreaks.com Questions, Comments, & Suggestions section.

 

Cheers