Room viewing system

[whit89]

Hi im attempting to create a room viewing system where by

The building selected with a drop down - this uses an sql query to store the data of the building from a different table rooms

then if its available from now ie rooms free for 1 hour from now

a drop down that gives the option of picking software

Any help would be great im struggling with the table linking and hourly system mainly.

then making a pie chart that dynamic data takes data from inuse in use being binary 0 not in use 1 in use

so far i have

buildings

building_id
building

room

room_id
software
booked
book_start
book_end

pc
pc_id
in_use

[pritaeas]

Shouldn't there be a link from room to building and from pc to room ?

[whit89]

buildings

building_id
building

room

room_id
software
booked
book_start
book_end
building_id

pc
pc_id
room_id
in_use

[whit89]

this is the end result i want book withou having to actually book just view

[diafol]

I don't get the software field. Are you only allowed to use one piece of software at a booking? What's the purpose of this? For stats?

Also, is this booking per individual or does an user book out an entire room at a time (e.g. teacher for a class)?

[whit89]

As such
The user wants to use a specific software ie dreamweaver

the user picks building say building one , the code then populates a second drop down with all the software , storing the building choice in a variable , they then select a time they want to use the room (from now or a set time) for a set amount the rroms are then displayed.

Ie ; Building;Building 1 Software:Dreamweaver Time;from 2:00 Duration; 2 hours

Result

Room : 1002 , available from:2:00 till:4:00
Room : 1003 , available from: 3:00 till: 5:00

[diafol]

OK, got that - but as I asked, does an user book out the entire room or just one PC?

From my point of view the building/room is secondary to my wishes to use a particular piece of software (or series of pieces of software).

So I'd suggest that you have that as your primary search along with date/times (and perhaps the min no. of PCs). That search should then give a list of available rooms (with buildings) that meet those criteria (along with radio buttons). The user then just clicks a submit button after making a choice -> confirmation page. My 2p.

[whit89]

There is no actual booking its more of a data system to view free rooms that are no being used.

The rooms are timetabled and what you are looking for are rooms with gaps so this is what this app does it searches for space so you can go use the room when its not being used.

[diafol]

OK. That means you need a 'timetable' table, e.g.

tt_id
room_id (FK)
dayofweek (int), e.g. 1=Mon, 5=Fri
starttime
endtime
tt_details

[whit89]

shouldn't i use date rather than int and varchar for day f week and the times?

[whit89]

tt_id
room_id (FK)
dayofweek (int), e.g. 1=Mon, 5=Fri
starttime
endtime
tt_details

so if i wanted slots

its would be

room id 3 date 24/12/11 start: 12:00 end:2:00
toom id 2 date 24/12/11 start: 2:00 end:4:00

[diafol]

shouldn't i use date rather than int and varchar for day f week and the times?

No as these classes are timetabled indefinitely from what I gather from your posts. So ascribing a date would not make sense. However, your could have a date_start and date_end if you really wanted, in the case that a course starts after the beginning of the academic year or ends before the end of the academic year.

In this case, you may wish to further normalise the tables to allow a courses table (with start and end dates) - you'd then just key in a course_id to the timetable table for each period.

The bookings is separate to the timetable. The timetable is purely there to block bookings. In order to check availability, you'd need to check both timetable and bookings tables. The bookings table would be the only one where you'd need to check by date.

BTW - I wrote a site similar to this a while back which had to take a fortnightly timetable into consideration. SO the timetable had to have a week_cycle field (1 or 2) as well and then I had to have a 'weeks' table, which had a startdate (first Monday of the week) and a week_cycle. So, any type of combination could be used, but it may mean adding more tables and using some tricky JOINS in your SQL.

Hope that helps.

[whit89]

The days are broken up into hourly blocks and i was just thinking as if i wanted to break it up and feed it into a calender ill post what i have s far

[diafol]

OK, that helps by using start time and end time as integers (0 - 23). Otherwise you'll be messing with all sorts of time conversion and nonsense.

So for your timetable table:

tt_id
room_id (FK)
dayofweek (int), e.g. 1=Mon, 5=Fri
starttime (int), e.g. 10 for 10:00
endtime (int), e.g. 19 for 19:00 (I assume midnight will never be used - otherwise this will have to be 24 for endtime - which technically doesn't exist)
tt_details

You can now check for a slot between these times via integer comparison - nice :)

BTW, if you have universal 'periods' or 'lessons' on the timetable, you could forget start time and end time and just use 'period', which would be even easier.

[whit89]

Ive seen a similar system but with a booking feature building in javascript/AJAX what advantage to i have with using php/AJAX other than my php knowledge is better?

[diafol]

You can't use js instead of php as js won't have access to your DB - and you'll most certainly need to use a DB.

Using js/Ajax interface can improve the overall experience for the user, but it is certainly not essential.

I'd code the thing in vanilla php to begin with - place as much code into functions or classes so that you can call them easily via ajax later on. If you use ajax, remember to code in such a way as to allow form submissions etc, if an user has js turned off. Using a framework/library like jQuery will make things a lot easier for you - e.g. dealing with different browsers.

I assume you'd need to use js/ajax for:

displaying a list of available periods/rooms etc when you submit the filters.
submitting the booking - however - this may be better via traditional submit.
displaying tabbed timetables on change date.

EDIT

right now im trying to filter the second box upon the selection of the first.

As I mentioned, jQuery has nice easy ways to do these sorts of things.

[whit89]

Hmm so dont do what i did before and but just search off of the id numbers when the form is submitted?

[whit89]

My main reason was on change for the first box i need the second box to only show buildings in that area and so on.

How do i do that in php?

on change $query="SELECT area_id,building FROM building WHERE area_id='$area'"; ?
$being set by the previous box.