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

[Phil Turmel]

Burhan Khalid wrote:
> Burhan Khalid wrote:
>> Ken Schaefer wrote:
>>> Can you post a set of sample data, and what your expected output is? 
>>> If you
>>> can provide that, then we can write the necessary SQL (if it's 
>>> possible) to
>>> generate the output.
> 
> Here is the information :
> 
> CREATE TABLE `events` (
>   `id` int(11) NOT NULL auto_increment,
>   `name` varchar(255) NOT NULL default '',
>   `start_date` date NOT NULL default '0000-00-00',
>   `end_date` date NOT NULL default '0000-00-00',
>   `etype` int(11) NOT NULL default '1',
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> 
> Sample data ( id | name | start_date | end_date ):
> 
> 13 | burhan event | 2005-06-08 | 2005-06-10
> 15 | burhan       | 2005-05-30 | 2005-06-05
> 16 | Expo         | 2005-06-28 | 2005-07-22
> 
> 
> CREATE TABLE `halls_events` (
>   `id` int(11) NOT NULL auto_increment,
>   `hallid` int(11) default NULL,
>   `eventid` int(11) default NULL,
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> 
> Sample data ( hallid | eventid ):
> 
> 2 | 13
> 4 | 13
> 2 | 15
> 2 | 16
> 
> *Note that an event can take place in two halls.
> 
> Now, what I'm trying to find is the first available 7 day gap for each 
> hall.  So for hall 2, the latest free date is 2005-06-11, or one date 
> after the end of event 13.
> 
> My idea was to find a set of events where the gap between the end date 
> and start date is 7 days, limit it to the first event.
> 
> I just can't seem to wrap my head around the logic.
> 

Burhan,

Maybe something like this, with $hall substituted using Your Favorite 
Scripting Language (tm):

SELECT before.end_date, after.start_date
     FROM (SELECT events.id, events.end_date
	FROM events INNER JOIN hall_events
	    ON events.id=hall_events.eventid
	WHERE events.end_date > CURDATE()
	    AND hall_events.hallid=$hall)
	AS before,
	(SELECT events.id, events.start_date
	FROM events INNER JOIN hall_events
	    ON events.id=hall_events.eventid
	WHERE events.end_date > CURDATE()
	    AND hall_events.hallid=$hall)
	AS after
     WHERE DATEDIFF(after.start_date,before.end_date)>7
     ORDER BY DATEDIFF(after.start_date,before.end_date)
     LIMIT 1

** Untested **. needs MySQL v4.1 or later

This doesn't return anything where no sufficient gap exists... then you 
must query for the last end_date for the hall.

HTH,

Phil