SQL Query to select upcoming events with a start and end date
- by Chris T
I need to display upcoming events from a database. The problem is when I use the query I'm currently using any events with a start day that has passed will show up lower on the list of upcoming events regardless of the fact that they are current
My table (yaml):
columns:
title:
type: string(255)
notnull: true
default: Untitled Event
start_time:
type: time
end_time:
type: time
start_day:
type: date
notnull: true
end_day:
type: date
description:
type: string(500)
default: This event has no description
category_id: integer
My query (doctrine):
$results = Doctrine_Query::create()
->from("sfEventItem e, e.Category c")
->select("e.title, e.start_day, e.description, e.category_id, e.slug")
->addSelect("c.title, c.slug")
->orderBy("e.start_day, e.start_time, e.title")
->limit(5)
->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
Basically I'd like any events that is currently going on (so if today is in between start_day and end_day) to be at the top of the list. How would I go about doing this if it's even possible? Raw sql queries are good answers too because they're pretty easy to turn into DQL.
