CODEWITHSUNDEEP
mysqldate
KerrM
KerrM

Reputation: 5240

How to get a query that only displays items that are in the present or future in MySQL?

I have this query:

$events = db_query("SELECT e.name, e.event_date, e.time, e.data, e.picture, e.event_type, v.latitude, v.longitude, v.address, v.name as vname 
FROM events e INNER JOIN venues v ON e.vid=v.vid 
WHERE FIND_IN_SET('".$q."', event_type)>0 
AND e.event_date > (SELECT DATE_SUB(curdate(), INTERVAL 1 DAY)) 
GROUP BY e.name");

And it all works fine except the date selection. I would like this query to list only items that are of the day before or in the future but at the moment it lists all items. Any pointers as to where I'm going wrong?

EDIT: If I remove the first "WHERE" clause then the query executes correctly and only returns the items with a date in the future.

The whole query looks like this:

SELECT e.name, e.event_date, e.time, e.data, e.picture, e.event_type, v.latitude, v.longitude, v.address, v.name as vname 
FROM events e INNER JOIN venues v ON e.vid=v.vid 
WHERE FIND_IN_SET('liveSports', event_type)>0 
OR FIND_IN_SET('dancing', event_type)>0 
OR FIND_IN_SET('drinksDeals', event_type)>0 
OR FIND_IN_SET('pubQuiz', event_type)>0 
OR FIND_IN_SET('boardGames', event_type)>0 
OR FIND_IN_SET('fussball', event_type)>0 
OR FIND_IN_SET('speedDating', event_type)>0 
OR FIND_IN_SET('pool', event_type)>0 
OR FIND_IN_SET('liveMusic', event_type)>0 
OR FIND_IN_SET('foodDeals', e.event_type)>0
AND e.event_date >= (SELECT DATE_SUB(curdate(), INTERVAL 1 DAY))
GROUP BY e.name

I have a feeling that the issue might be with the amount of ORing going on here. If I reduce the size of the query in that way, the results are returned as expected.

Thanks,

Upvotes: 3

Views: 81

Answers (2)

Andrew
Andrew

Reputation: 4624

You were very close, but I bet you wanted to OR all the FIND_IN_SETs together, THEN check the date on all of them. I added parens. Also, you didn't need the subquery (SELECT...) arround the DATE_SUB.

SELECT e.name, e.event_date, e.time, e.data, e.picture, e.event_type, v.latitude, v.longitude, v.address, v.name as vname 
FROM events e INNER JOIN venues v ON e.vid=v.vid 
WHERE (FIND_IN_SET('liveSports', event_type)>0 
OR FIND_IN_SET('dancing', event_type)>0 
OR FIND_IN_SET('drinksDeals', event_type)>0 
OR FIND_IN_SET('pubQuiz', event_type)>0 
OR FIND_IN_SET('boardGames', event_type)>0 
OR FIND_IN_SET('fussball', event_type)>0 
OR FIND_IN_SET('speedDating', event_type)>0 
OR FIND_IN_SET('pool', event_type)>0 
OR FIND_IN_SET('liveMusic', event_type)>0 
OR FIND_IN_SET('foodDeals', e.event_type)>0)
AND e.event_date >= DATE_SUB(curdate(), INTERVAL 1 DAY)
GROUP BY e.name

Upvotes: 1

Gangnus
Gangnus

Reputation: 24464

Use 

e.event_date > UNIX_TIMESTAMP(subdate(current_date, 1))

or, for example: 

e.event_date > subdate(NOW(), INTERVAL 1 DAY)

BTW, it would be fine, to strip your SELECT of all conditons except Date, maximally simplify it and then make it to work. After that return back other SELECT elements.

Upvotes: 1

Related Questions