SQL query problems

[furious5]

Can anyone help me out.

I seem to be struggling with an SQL query, I've written.

Basically I want to find any events that are due to take place within the next week.

At the moment I am using:

SELECT event.id, event.venue_id, event.name AS fixture, event.startdate, event.enddate, event.country, venue.name AS course
FROM event, venue
WHERE CURDATE() <= event.startdate 
AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) >= event.enddate 
AND event.event_status_id =0 
AND event.venue_id = venue.id

[Roger_Ramjet]

And what do you get? If it is an error then paste the error message, if it is the wrong data then table structure and results would help.

[furious5]

Basically I am getting no results back, although I know that there is an event taking place between 11-11-2004 and 14-11-2004. Therefore by rights I should be returning a valid event.

[Roger_Ramjet]

Use

$results = mysql_query($sql) or die($sql . '<br />' . mysql_error());

to see if you are getting an error, the die(...) will print any error message to the screen.

If no error message then problem solving strategy number 1: break it down.

Run

SELECT event.id, event.venue_id, event.name AS fixture, event.startdate, event.enddate, event.country, venue.name AS course
FROM event, venue
WHERE CURDATE() <= event.startdate

and see what it returns, then add each AND clause untill it goes wrong, then you know what to fix.

[furious5]

There is no error in the SQL, its just how I am structuring the query of the date ranges.

If I run the query as suggested above, I simply return all results with a greater date range than today

[tsinka]

Hi,

your example contains yesterday as start date but your query only fetches events starting today or later which is the problem here. What about this query:

SELECT event.id, event.venue_id, event.name AS fixture, event.startdate, event.enddate, event.country, venue.name AS course
FROM event, venue
WHERE (event.startdate <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND event.startdate >= CURDATE())
OR (event.enddate <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND event.enddate>=CURDATE()) 
AND event.event_status_id =0 
AND event.venue_id = venue.id

Thomas

[furious5]

Thanks for this, but its still not right. I'm really confused with MySQL's handling of date comparision.

Basically I want to only return events which take place this week.

I wrote this SQL which seems to do what I want, however everything is based around the enddate, which doesn't seem flexible enough.

SELECT event.id, event.venue_id, event.name AS fixture, event.startdate, event.enddate, event.country, venue.name AS course
FROM event, venue
WHERE CURDATE() < event.enddate 
AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) > event.enddate 
AND event.event_status_id =0 
AND event.venue_id = venue.id