Retriving Records From Past Dates

[Bopo]

Hi

 

Hi

 

Basically I want to write a query which returns records from the past 7 days, I have a DATE field which stores dates in the following format 2009-04-08 (standard format), heres the query below

 

<?php if($getdate == "7day") {
$day = 7;
$sql = "SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '$day' DAY) <= $todaysdate";  //date = column field in table

echo $sql;
}?>

 

Here's what is output from the echo

 

SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '7' DAY) <= 04.08.09

 

Error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

[rhodesa]

$todaysdate needs single quotes around it and needs to be in YYYY-MM-DD format

[Bopo]

Thanks for the reply, I thought that would definitely fix the problem, however I'm still getting the same error, could it be something to do with my column being called date? here is the new sql output

 

SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '7' DAY) <= '2009/04/08'

[revraz]

2009-04-08 not 2009/04/08

[rhodesa]

also, when you call your query, make sure you use mysql_error():

 

mysql_query($sql) or die(mysql_error());

[Bopo]

Thanks for the suggestions, I have changed the date format to the correct format, also I implemented the mysql_error function for my query, here is what I got back:

 

SELECT * FROM comments WHERE date(CURDATE(), INTERVAL '7' DAY) <= '2009-04-08'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 ' INTERVAL '7' DAY) <= '2009-04-08'' at line 1

 

 

[revraz]

Remove the single quotes around 7 and see if that resolves it.

[Bopo]

Thanks for the reply, sadly I get the same error, the only thing that is concerning me is that all the example I have seen which are similar to mine have DATE_FUNCTION where my column name 'date' is.

[revraz]

Well put back ticks around it and find out, but date shouldn't be a reserved word for mysql.

 

What version of mysql are you on, it almost looks like it doesn't recognize INTERVAL.

[Bopo]

Nah the back ticks didn't change anyway, I'm using MySQL client version: 5.0.67

 

As a last resort, would using a between clause work between two dates, like:

 

select * from comment where date between $todaysdate and $7daysago

 

How would you minus 7 days from today's date variable though?