Find date range

I want to create php/mysql code where a user enters a date for example 2008-10-20) and the script returns all results in the database for 7 days before that date and 3 days after that date.

I've tried a few things that didnt work, any ideas?

Thanks

 

What is the column type for the date field in your database (date, datetime, timestamp)?

 

It's date

Thanks

 

assume date column name is theDate, and the Table name is theTable

The sql would be similar to this:

SELECT * FROM theTable
WHERE datediff(day,theDate,date(searchDate))<=3 and datediff(day,theDate,date(searchDate))>=-7


of course, you would replace searchDate with whatever variable your user puts into the form.

~fm

 

Thanks

I tried that and got this error message

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 'date(2008-01-18))<=3 and datediff(day,ExamDate,date(2008-01-18))>=-7 ORDER BY Ex' at line 1

I ran the query in phpmyadmin and it gave the error number 1064

I'm running PHP Version 4.4.7 and mysql version 5.0.67-community if it helps

 

Kain,

the arguments of the "Date" function have to be string values, and therefore have to be enclosed in single quotes.

so it would be date('2008-01-18'))



~Fm

 

I tried that but still no luck, here is the error message:

#1064 - 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 'date('2008-10-07'))<=3 and datediff(day,ExamDate,date('2008-10-07'))>=-7 ORDER B' at line 1

The full query is

$result=mysql_query("SELECT * FROM results WHERE datediff(day,ExamDate,date('2008-10-07'))<=3 and datediff(day,ExamDate,date('2008-10-07'))>=-7 ORDER BY ExamDate ASC") or die(mysql_error());