[thelist] Iterate through result set - Is this possible? (SQL)
Phil Turmel
philip at turmel.org
Tue May 31 14:58:07 CDT 2005
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
More information about the thelist
mailing list