CODEWITHSUNDEEP
mysqlsqldatabase
Danny
Danny

Reputation: 343

If statement within mySql query

I've spent a lot of time searching for an answer to this problem online but I can't seem to come up with anything useful.

I'm pretty new to mySQL so I have a limited enough knowledge.

I have a table events with 6 columns: id, event_name, event_date, event_close_time, event_link, and event_image.

I'm trying to select everything from the database where the event_close_time is < current time if the event is on today and select all the other events in the future.

What I came up with is:

SELECT * FROM `events` 
WHERE `event_date` >= 'todays_date'
AND `event_close_time` >  'current_time'
ORDER BY `event_date`

but this doesn't do exactly what I want it to do. It returns all events that are on today or in the future as long as their closing time is earlier then the current time.

It's important that I do it in 1 query because the ORDER BY clause at the end allows me to sort the events into the order I want to use in my web application.

Could anyone point out the adjustments I need to make to get the desired result?

Upvotes: 0

Views: 391

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You should do this with one select and the correct where clause:

SELECT *
FROM `events` 
WHERE (event_date = CURDATE() AND event_close_time < CURTIME()) or
      (event_date > CURDATE())
ORDER BY `event_date`;

Implementing or logic by using union or union all is a bad habit to get into. For one thing, it requires scanning the events table twice, once for each condition. The single where only scans the table once.

Upvotes: 1

Seth
Seth

Reputation: 1373

How about this?

SELECT *
FROM events
WHERE event_date = CURDATE()
AND event_close_time < CURTIME()
UNION ALL 
SELECT * 
FROM events 
WHERE event_date > CURDATE()
ORDER BY event_date

This could also be accomplished with an OR instead of the UNION ALL, but I have found that UNIONS tend to be more efficient because ORs cause indexes not to be used (at least in MySQL).

Upvotes: 0

Related Questions