[PHP] Diary system help

[Thsharp01]

Just having a bit of a problem that i can't for the life of me get my head around, i know it can't be overly complicated but if anyone can give me a hand i would most appreciate it!

Quick overview of the setup:

table - event

SELECT * from event WHERE event_type='$_POST[frmType]' AND event_max >='$_POST[frmDelegates]'

this returns a list of room ids that:

1) have the correct capacity
2) handles the type of event

Then the code needs to check if the room is available. This information
is stored in

table - diary

two columns in this table are called 'start_date' and 'end_date'

the dates are stored in unix timestamp format

SELECT * FROM diary WHERE (start_date >= '$begin' AND start_date <= '$end') 
OR (end_date <= '$end' AND  end_date >= '$begin') ORDER BY venue_id ASC

this query in itself returns the correct data, it will return
a row if a particular booking is on or inbetween the dates stored.

The problem i am having, how do i get it to return a list that:

1) displays rooms that are available
2) displays rooms that are unavailable

Any help is greatly appreciated!

Thanks.

 

[LazyManc]

We need a bit more info on your table structure - I'm assuming the diary table holds a foreign key to the event table?

Also a bit confused - you say that the select on the event table returns a list of rooms?

 

[Thsharp01]

Sorry for the lack of information, i was rushing to post at the time.

The dairy table has a column called 'venue_id' which relates to a column of the same name in the events table.

Table - Diary
[id] [venue_id] [start_date] [end_date] [client_name]

Table - Event
[id] [venue_id] [event_type] [event_max]

Table - Venue
[id] [venue_id] [venue_name]

An example set of data would be:
venue_name = Ballroom
venue_id = 1

event_type = Conference (Type of event this room can handle)
event_max = 2000 (How many delegates the room can hold)

start_date = 1196485200 (1st December 2007)
end_date = 1196830800 (5th December 2007)
client_name = Me

Obviously with this set of data, once i input my data into my form, and select that i want a Conference from 1st - 5th December, i want it to tell me that the Ballroom is unavailable.

Hopefully i've gone into a bit more detail

Thanks anyway!

 

[SiriusB]

So simply put you want a list of ALL the rooms that will fit the event, but obviously split into those that are available and those that aren't?

 

[Thsharp01]

Yes, thats pretty much the jist of what i want it to do. Like an availability checker..

 

[chesterstu]

The problem i am having, how do i get it to return a list that:

1) displays rooms that are available
2) displays rooms that are unavailable

Any help is greatly appreciated!

Thanks.

Do an outer join of with diary on the right and venues on the left, if the diaryid coloumn is null then the room is free else it is in use. Obviously using the where clause to select the correct dates for your events. You can also inner join on the event table to get the details of the event

 

[Thsharp01]

Managed to get this working, probably not the best way to do this but it does what i want and it doesn't need to be particularly optimised.

I did use a join, never used them before with mysql and can't believe what i've been missing Cheers chesterstu!

Anyway, heres the code i used if anyone is interested:

$q1 = mysql_query("SELECT * FROM event 
			            JOIN venue 
			            ON event.venue_id = venue.venue_id 
			            WHERE event.event_type='$_POST[frmType]' AND event.event_max >='$_POST[frmDelegates]'");
				   
while ($et = mysql_fetch_array($q1)) {

	$q2 = mysql_query("SELECT * FROM diary 
					   WHERE (start_date >= '$begin' AND start_date <= '$end' AND venue_id='$et[venue_id]') 
					   OR (end_date <= '$end' AND  end_date >= '$begin' AND venue_id='$et[venue_id]')");
	
	if (mysql_num_rows($q2) == true) {
	
		echo "$et[venue_name] - Unavailable<BR><BR>";
		
	} else {
	
		echo "$et[venue_name] - <B>Available</B><BR><BR>";
		
	}				   
	

}

So basically the first query gets a list of all the rooms that can handle the event type and fit in the number of delegates in. Then it while loops through the results checking if there is an entry in the diary table between the selected dates, if there is its unavailable, if not, available!

Thanks for your help guys!

P.S. ignore variable names