Reducing many SQL queries for this simple code

Question

I have a database table containing dates when events start. An event ends when the next one starts. Given a start date and end date, I'm looking to create an array of dates with their corresponding event ID.

Currently I run an SQL query for each date. Pseudocode:

$currDate = $startDate
While $currDate++ < $endDate
  echo $currDate . " - " . dbQuery("SELECT TOP 1 ID FROM events WHERE eventDate <= '$currDate' ORDER BY eventDate DESC");
Loop

How can I make this more efficient (in PHP)?

EDIT: Thanks for the answers however I realise the question wasn't clear. Managed to write some SQL that executes fast (<10ms) on my server for dates spanning years.

Answer 1

Here's the solution using SQL:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Create Table dbo.Events (
  ID int not null primary key nonclustered,
  EventDate datetime not null
);


Insert Into dbo.Events (ID, EventDate) Values
  (1, '2011-11-19'),
  (2, '2011-11-25'),
  (3, '2011-12-12'),
  (4, '2012-01-02'),
  (5, '2012-01-05'),
  (6, '2012-02-10');

Query 1:

DECLARE @dtStart DATETIME,
        @dtEnd   DATETIME

SET @dtStart = '2011-11-01'
SET @dtEnd = '2012-02-20'

SELECT thedate,
       eventID = (SELECT TOP 1 id
                  FROM   events
                  WHERE  eventdate <= thedate
                  ORDER  BY eventdate DESC)
FROM   (SELECT Dateadd(DAY, number, @dtStart) AS theDate --build dates from the start to end date
        FROM   (SELECT DISTINCT number
                FROM   master.dbo.spt_values
                WHERE  name IS NULL) n --returns integer values from 0 to 2037
        WHERE  Dateadd(DAY, number, @dtStart) < @dtEnd) x

Results:

|                         THEDATE | EVENTID |
---------------------------------------------
| November, 01 2011 00:00:00+0000 |  (null) |
| November, 02 2011 00:00:00+0000 |  (null) |
| November, 03 2011 00:00:00+0000 |  (null) |
| November, 04 2011 00:00:00+0000 |  (null) |
| November, 05 2011 00:00:00+0000 |  (null) |
| November, 06 2011 00:00:00+0000 |  (null) |
| November, 07 2011 00:00:00+0000 |  (null) |
| November, 08 2011 00:00:00+0000 |  (null) |
| November, 09 2011 00:00:00+0000 |  (null) |
| November, 10 2011 00:00:00+0000 |  (null) |
| November, 11 2011 00:00:00+0000 |  (null) |
| November, 12 2011 00:00:00+0000 |  (null) |
| November, 13 2011 00:00:00+0000 |  (null) |
| November, 14 2011 00:00:00+0000 |  (null) |
| November, 15 2011 00:00:00+0000 |  (null) |
| November, 16 2011 00:00:00+0000 |  (null) |
| November, 17 2011 00:00:00+0000 |  (null) |
| November, 18 2011 00:00:00+0000 |  (null) |
| November, 19 2011 00:00:00+0000 |       1 |
| November, 20 2011 00:00:00+0000 |       1 |
| November, 21 2011 00:00:00+0000 |       1 |
| November, 22 2011 00:00:00+0000 |       1 |
| November, 23 2011 00:00:00+0000 |       1 |
| November, 24 2011 00:00:00+0000 |       1 |
| November, 25 2011 00:00:00+0000 |       2 |
| November, 26 2011 00:00:00+0000 |       2 |
| November, 27 2011 00:00:00+0000 |       2 |
| November, 28 2011 00:00:00+0000 |       2 |
| November, 29 2011 00:00:00+0000 |       2 |
| November, 30 2011 00:00:00+0000 |       2 |
| December, 01 2011 00:00:00+0000 |       2 |
| December, 02 2011 00:00:00+0000 |       2 |
| December, 03 2011 00:00:00+0000 |       2 |
| December, 04 2011 00:00:00+0000 |       2 |
| December, 05 2011 00:00:00+0000 |       2 |
| December, 06 2011 00:00:00+0000 |       2 |
| December, 07 2011 00:00:00+0000 |       2 |
| December, 08 2011 00:00:00+0000 |       2 |
| December, 09 2011 00:00:00+0000 |       2 |
| December, 10 2011 00:00:00+0000 |       2 |
| December, 11 2011 00:00:00+0000 |       2 |
| December, 12 2011 00:00:00+0000 |       3 |
| December, 13 2011 00:00:00+0000 |       3 |
| December, 14 2011 00:00:00+0000 |       3 |
| December, 15 2011 00:00:00+0000 |       3 |
| December, 16 2011 00:00:00+0000 |       3 |
| December, 17 2011 00:00:00+0000 |       3 |
| December, 18 2011 00:00:00+0000 |       3 |
| December, 19 2011 00:00:00+0000 |       3 |
| December, 20 2011 00:00:00+0000 |       3 |
| December, 21 2011 00:00:00+0000 |       3 |
| December, 22 2011 00:00:00+0000 |       3 |
| December, 23 2011 00:00:00+0000 |       3 |
| December, 24 2011 00:00:00+0000 |       3 |
| December, 25 2011 00:00:00+0000 |       3 |
| December, 26 2011 00:00:00+0000 |       3 |
| December, 27 2011 00:00:00+0000 |       3 |
| December, 28 2011 00:00:00+0000 |       3 |
| December, 29 2011 00:00:00+0000 |       3 |
| December, 30 2011 00:00:00+0000 |       3 |
| December, 31 2011 00:00:00+0000 |       3 |
|  January, 01 2012 00:00:00+0000 |       3 |
|  January, 02 2012 00:00:00+0000 |       4 |
|  January, 03 2012 00:00:00+0000 |       4 |
|  January, 04 2012 00:00:00+0000 |       4 |
|  January, 05 2012 00:00:00+0000 |       5 |
|  January, 06 2012 00:00:00+0000 |       5 |
|  January, 07 2012 00:00:00+0000 |       5 |
|  January, 08 2012 00:00:00+0000 |       5 |
|  January, 09 2012 00:00:00+0000 |       5 |
|  January, 10 2012 00:00:00+0000 |       5 |
|  January, 11 2012 00:00:00+0000 |       5 |
|  January, 12 2012 00:00:00+0000 |       5 |
|  January, 13 2012 00:00:00+0000 |       5 |
|  January, 14 2012 00:00:00+0000 |       5 |
|  January, 15 2012 00:00:00+0000 |       5 |
|  January, 16 2012 00:00:00+0000 |       5 |
|  January, 17 2012 00:00:00+0000 |       5 |
|  January, 18 2012 00:00:00+0000 |       5 |
|  January, 19 2012 00:00:00+0000 |       5 |
|  January, 20 2012 00:00:00+0000 |       5 |
|  January, 21 2012 00:00:00+0000 |       5 |
|  January, 22 2012 00:00:00+0000 |       5 |
|  January, 23 2012 00:00:00+0000 |       5 |
|  January, 24 2012 00:00:00+0000 |       5 |
|  January, 25 2012 00:00:00+0000 |       5 |
|  January, 26 2012 00:00:00+0000 |       5 |
|  January, 27 2012 00:00:00+0000 |       5 |
|  January, 28 2012 00:00:00+0000 |       5 |
|  January, 29 2012 00:00:00+0000 |       5 |
|  January, 30 2012 00:00:00+0000 |       5 |
|  January, 31 2012 00:00:00+0000 |       5 |
| February, 01 2012 00:00:00+0000 |       5 |
| February, 02 2012 00:00:00+0000 |       5 |
| February, 03 2012 00:00:00+0000 |       5 |
| February, 04 2012 00:00:00+0000 |       5 |
| February, 05 2012 00:00:00+0000 |       5 |
| February, 06 2012 00:00:00+0000 |       5 |
| February, 07 2012 00:00:00+0000 |       5 |
| February, 08 2012 00:00:00+0000 |       5 |
| February, 09 2012 00:00:00+0000 |       5 |
| February, 10 2012 00:00:00+0000 |       6 |
| February, 11 2012 00:00:00+0000 |       6 |
| February, 12 2012 00:00:00+0000 |       6 |
| February, 13 2012 00:00:00+0000 |       6 |
| February, 14 2012 00:00:00+0000 |       6 |
| February, 15 2012 00:00:00+0000 |       6 |
| February, 16 2012 00:00:00+0000 |       6 |
| February, 17 2012 00:00:00+0000 |       6 |
| February, 18 2012 00:00:00+0000 |       6 |
| February, 19 2012 00:00:00+0000 |       6 |

Answer 2

You can use a similar query like below, this way you'll retrieve the whole list of event ids, date start and date end at once:

select id, date_start,(select min(date_start) from eventss where date_start > e.date_start) as date_end from
eventss e where date_start >= @DateStart
and 
(select min(date_start) from eventss 
where date_start > e.date_start) <= @DateEnd

Answer 3

select distinct
e1.eventDate,
ID = (
        select top 1 e2.ID 
        from Events e2 
        where e2.eventDate <= e1.eventDate 
        order by e2.eventDate desc
    )
from 
Events e1

When you get the results from the query, you would them loop them in your code and display the results. The key to making your implementation more efficient is to reduce the number of database roundtrips.