[thelist] Iterate through result set - Is this possible? (SQL)

[Phil Turmel]

Joshua Olson wrote:
> Allow me to rewrite your query, substituting some lines to make it easier to
> understand.
> 
> SELECT before.end_date, after.start_date
>      FROM ([All Events After Right Now For $hall]) AS before,
> 	([All Events After Right Now For $hall]) AS after
>      WHERE DATEDIFF(after.start_date,before.end_date)>7
>      ORDER BY DATEDIFF(after.start_date,before.end_date)
>      LIMIT 1
> 
> Now the query is much less complex.  It's essentially comparing all upcoming
> events against ALL other upcoming events.
> 
> I'm not sure this is going to return what you want, ultimately.
> 
> Let's say that there are two events for a hall that are separated by 14
> days.  Conceptually, that will be returned by this query even if every day
> in between them is booked by other events.

Joshua,

You are absolutely correct.  My mind was already jumping ahead to the 
grouping solution posted later.  The grouping solution is almost 
correct...  it needs to accept all ordered pairs regardless of gap to 
compute the gapsize, then apply a HAVING clause to strip out the ones 
that are too short.

Like so:

As before, $hall and $gapneeded substituted using Your Favorite 
Scripting Language (tm):

SELECT DATE_ADD(before.end_date,INTERVAL 1 DAY) AS gapstart,
   MIN(DATEDIFF(after.start_date,before.end_date)-1) AS gapsize
   FROM (SELECT events.id, events.end_date
       FROM `events` INNER JOIN halls_events
         ON events.id=halls_events.eventid
       WHERE events.end_date > CURDATE()
         AND halls_events.hallid=$hall)
     AS `before`,
     (SELECT events.id, events.start_date, events.end_date
       FROM `events` INNER JOIN halls_events
         ON events.id=halls_events.eventid
       WHERE events.end_date > CURDATE()
         AND halls_events.hallid=$hall)
     AS `after`
   WHERE after.end_date>before.end_date
   GROUP BY DATE_ADD(before.end_date,INTERVAL 1 DAY)
   HAVING DATEDIFF(after.start_date,before.end_date)>$gapneeded

TESTED on MySQL 5 w/ $hall = 2, $gapneeded = 7, and the data posted 
earlier... (had to fix some typos and add ticks to events, before, and 
after)

Returned:

gapstart 	gapsize
2005-06-11	17

Comments?

Burhan, care to try it?

Phil