Queries to display timed content

[dave1236]

All:

I am trying to write a query that only returns data meeting certain criteria, date being one.

I have a table called Links.
Field called: entry, link, expire, category

I am trying to write a query that only returns links meeting a certain category, with an expiration in the future, and that has been entered within the last five days.

I have tried a number of queries, and before I totally destroy my files, this is what I have finished with:

$today = date("Y-m-d");
$importantDate = strtotime("now - 5 days");
$category = "sports";
$query = "SELECT * FROM Links WHERE category='$category' AND expire > '$today' AND entry >= '$today - $importantDate'";

The first three parts work fine - it is when I get to the last AND that things get screwed up. Is it syntax, or am I missing something?

I would like it to return the rows where category= sports AND expire is in the future, and the link was entered within the last five days!

Thanks!

[barns101]

$today = date("Y-m-d") will give you a format of 2006-08-31 (for example) but $importantDate is a Unix timestamp. I could be wrong, but I don't think that you can subtract one from the other, as they are in different formats. I would use a Unix timestamp for both.

So you would have this, which although not tested should work:

$today = mktime(); // Current Unix timestamp
$importantDate = strtotime("now - 5 days"); // Unix timestamp from 5 days ago
$category = "sports";
$query = "SELECT * FROM Links WHERE category='$category' AND expire > '$today' AND entry >= '$today - $importantDate'";

Remember that the `expire` field must be a Unix timestamp, too.

[dave1236]

I tried this....it did not work. I am thinking because all the dates in my database are formatted in Y-m-d format?

[dave1236]

I am running on a Linux

[coopster]

$days = 5; 
$sql = " 
SELECT 
 * 
 FROM Links 
 WHERE 
  category = '$category' AND 
  expire > CURRENT_DATE AND 
  entry >= CURRENT_DATE - INTERVAL $days DAY 
";

[dave1236]

Thanks Coopster!