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

[Phil Turmel]

Phil Turmel wrote:
> Joshua Olson wrote:
>>> -----Original Message-----
>>> From: Phil Turmel
>>> 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?
>>
>> Phil,
>>
>> I don't have immediate access to MySQL 5, and I was wondering if you
>> wouldn't mind adding one thing...
>>
>> Add a new event from 6-12 to 6-24 and put that event into hall 2.
>>
>> What does the code return now? 
> 
> Returned no rows for $gapneeded = 7,
> For $gapneeded=1, it returns this:
> 
> gapstart    gapsize
> 2005-06-06    2
> 2005-06-25    3
> 
> Phil
>  

Joshua/Burhan,

Just for kicks, I added a union to the second subquery to create a 
virtual entry one year in the future, so an entry will be returned if 
there's any slot in the coming year.

Results for $gapneeded=7, and for $gapneeded=1 (and your extra event...)

gapstart	gapsize
2005-07-23	312

gapstart	gapsize
2005-06-06	2
2005-06-25	3
2005-07-23	312

New SQL Looks like this:

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
     UNION SELECT 0 AS id,
       DATE_ADD(CURDATE(),INTERVAL 1 YEAR) as start_date,
       DATE_ADD(CURDATE(),INTERVAL 1 YEAR) as end_date)
     AS `after`
   WHERE after.end_date>before.end_date
   GROUP BY DATE_ADD(before.end_date,INTERVAL 1 DAY)
   HAVING gapsize>$gapneeded


Regards,

Phil