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

[Burhan Khalid]

Phil Turmel wrote:
> 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
> 

Phil:

   Thanks again for your SQL wizardy, this really is wayyy beyond my SQL 
skills.  It works brilliantly!! I wasn't aware that you can alias entire 
result sets (as you did with `before` and `after`).

   One question -- what does UNION SELECT 0 AS id do? This is the only 
thing that I cannot understand.

   Now to digest all this so hopefully next time I can write something 
as elegant as this myself.

   Again, thank you very much.  You really saved me a lot of headaches.

With respect,
Burhan

<tip type="MySQL SQL Tools" author="Burhan Khalid">
  If you want a user friendly tool to test out your queries
and don't like the spartan command line MySQL client; try
the MySQL Query Browser (available from the MySQL download
site).  It has an excellent interface and easily allows
you to view results, paginate them, search through results,
view messages from MySQL -- and provides syntax highlighting
for SQL and a lot of other features.

  It even comes with inline help in the form of SQL documentation
from MySQL.  Should be a must for any person working with MySQL.  Plus, 
its free!

  Available as a Windows binary, Linux RPM (RH 9, SuSE 9.1) and source.
</tip>