Order By Date

[pgrevents]

I have a datetime stamp in my database how would I order only the last 7 days entries?

 

thanks

 

Paul

[pgrevents]

This field is an expiry field and well each item is 30 days so how would I refine to show the last entries in 7 days

 

[Maq]

SELECT * from table ORDER BY date DESC WHERE date > DATE_SUB(curdate(), INTERVAL 1 WEEK);

 

Depending on the order you want, the dates in you can use DESC (descending) or take it out which will, by default, make it ascending.

[pgrevents]

Thanks for that I have tried implamenting that ill show you my code;

 

<?php
$query = mysql_query("SELECT * from programme ORDER BY expiry DESC WHERE expiry > (curdate(), INTERVAL 1 WEEK)") or die(mysql_error());
if(empty($query)){
print "NOT VALID";
}

while($row=mysql_fetch_array($query, MYSQL_ASSOC)){
?>
<?=$row['pid']?><br/>
Expiry : <?=$row['expiry']?><br/>
<hr>
<? } ?>

 

This throws back an error which is

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE expiry > (curdate(), INTERVAL 1 WEEK)' at line 1

[Maq]

Sorry, you have to reverse the order of the clauses, my fault.  Also, if you only want 1 username returned, you have to specify that with the LIMIT clause.

 

$query = mysql_query("SELECT * from programme WHERE expiry > (curdate(), INTERVAL 1 WEEK) ORDER BY expiry DESC LIMIT 1") or die(mysql_error());

[pgrevents]

Sorry to be such a pain with this im a newbie

 

the changed code thows back another error ==  ) ORDER BY expiry DESC LIMIT 1' at line 1

[pgrevents]

I have another field time added which is formated like this

0 days 0 hours ago (1245170580)

would there be a way of showing all up to

7 days 0 hours ago (Rand)

[Ken2k7]

With the TIMESTAMP -

SELECT *
FROM programme
WHERE expiry >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
ORDER BY expiry DESC;

[pgrevents]

hi thanks for that but it displays the whole database but in date order is there away to show just the last seven days?

[Maq]

hi thanks for that but it displays the whole database but in date order is there away to show just the last seven days?

 

That's what this clause is supposed to filter out:

 

WHERE expiry >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)

 

Try to echo it out and see what's actually being passed to mysql_query.

 

$sql = "SELECT * FROM programme WHERE expiry >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) ORDER BY expiry DESC";
echo "Query => " . $sql;
$query = ($sql) or die(mysql_error());
if(empty($query)){