Prevent Double Booking

[farad]

I have a problem which is why I am here. 

 

What I am trying to achieve

 

I am creating a very very basic timetabling system online, using php and sql. I am still in the process of completing it and changing bits from here to there. Although I am fully aware that the current design / implementation needs several changes and amendments, but however it performs most of the basic functionalities from a login system to the ability to add data delete data and also reset the database and recreate.

 

 

The problem

 

I have a table called tCourse althouogh a full ERD implementation has not taken place, it is still trial and error period.

 

The table consists of the following:

 

- Course

- Unit

- Course_Code

- Year (i.e. Yr1, Yr2, Yr3)

- Credits (Value of the unit)

- Day

- Semester

- Start_Time

- End_Time

- Room

- Tutor

 

At the moment the primary keys for the table are: 

 

- Day

- Start_Time

- Room_ 

- Semester

 

This basically prevents a particular day, a semester, a room having been booked at the same time. Which for a very basic one is ok. The only problem is though,

 

if someone books for example:

 

Monday >> 13:00:00 To 14:00:00 >> 205 >> Sem1 (ok)

Monday >> 13:00:00 To 14:00:00 >> 205 >> Sem1 (Not ok, which is good, as it is a repeat and prevents double booking)

 

However the problem comes here:

 

 

Monday >> 12:00:00 To 14:00:00 >> 205 >> Sem1 (ok)

So this is allowing a booking even though that room will be busy i.e. booked between 13:00 to 14:00

 

So is there a way I can limit it, so if there is a room booked for that particular period it will not do it.

 

I have done a bit of research and friend's have suggested doind several for loops and quering the database beforehand. I came here, mainly because there are a lot of experienced individuals here whom may have a simpler solution, although I can understand it won't be a one liner .

 

I would appreciate any help, if not, it is still ok.

 

 

 

[MattDunbar]

I'm assuming you're dealing with timestamps as you didn't specify how times are stored, however this should be pretty simple for most other types.

 

Also, my queries are very basic and doesn't touch on days of the week - I've left a bit of the easier things for you to do yourself.

 

 

 

Then when doing the SQL select, try these queries:

 

-Find anything with a start time during the new period.  If count doesn't equal 0, it means that the time is taken.

SELECT COUNT(*) as count FROM tCourse WHERE Start_Time >= (the start timestamp) AND Start_Time <=  (the end timestamp)

 

 

-Find anything with a end time during the new period.  If count doesn't equal 0, it means that the time is taken.

SELECT COUNT(*) as count FROM tCourse WHERE End_Time >= (the start timestamp) AND End_Time <=  (the end timestamp)

 

Now, the tricky part is finding things that fit completely within another time period.

 

For example,

2PM - 6PM

and inside of it,

3PM - 4PM

 

To do that, we can use this query:

SELECT COUNT(*) as count FROM tCourse WHERE Start_Time < (starting timestamp) AND End_Time > (ending timestamp).

 

 

I'm pretty sure I've covered every scenario here, but I don't have the time to test it out myself right now.  If I missed anything, post it and I'll try to come up with a query for you.  You might also be able to combine a few of these queries into one by using OR statements.  Theres a few ways this could be optimized.

[litebearer]

what method are you using to allow users to choose open time? Seems that restricting choices to available time slots may be more efficient.

[farad]

Hi guys

 

Thanks for the reply.

 

I will have a play around with what is suggested.

 

field for time is defined as Time

 

I am allowing the user to input the time loke so.

 

11:00:00.

[farad]

Hello there, thank you very much for your input. Quite rude of me not to reply any sooner though have been busy. Your suggestion was taken on board though I went for a slight modification, although it functions not fully.

 

Instead of using the first method I went for using the sql numrows function.

 

$odbc = mysql_connect('localhost','root','') or die ("Can't connect to server");

mysql_select_db('tTime', $odbc) or die ("Database does not exist");

$sql ="select * from tModule where Semester_ = '$Semester' AND  Day_ ='$Day' AND Start_Time_ ='$Start_Time' AND

Room_ ='$Room'

";

 

$result = mysql_query($sql, $odbc) or die ("Can't run query");

 

if(mysql_num_rows($result)==0)

{

 

Run the insert code

}

 

if(mysql_num_rows($result)!=0)

{

 

Error out room is booked

 

}

 

That works perfectly fine and I have been testing it, not sure if that is the proper of way of doing it though.

The problem comes if there is a time between, i.e. if a room is booked from 9:00:00 11:00:00 amd prevent and bookings from 08:00:00 to 10:00:00

I would appreciate any inputs on that. Thanks

[farad]

Any suggestions.

[farad]

Any inputs pleaseee.