Time query help

farad · March 16, 2011

Hi there.

I am having issues trying to perform a query which needs to check if a room is already booked.

I created a a thread regarding this particular issue in this website but the suggested code did not work. I modified the code and tried several other methods, but the problem still persists.

I can prevent a double booking.

So for example if a room if booked for 11:00 to 12:00

and if you book the same time again for the particular room it will not work, i.e. stop data being inserted into the database.

Now the issue arises when you book a room for 10:00 to 12:00 when that room is already booked for 11:00 12:00.

I have tried to find the dusplicate time in the middle but hasn't helped.

The weird thing is it stops the majority of double booking but not every signle entry I use.

Here is the code below:

$sql =" Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'

AND Start_Time_ >= '$Start_Time' AND Start_Time_<='$End_Time'

AND End_Time_ >= '$Start_Time' AND End_Time_ <= '$End_Time'

";

btherl · March 17, 2011

This has a good explanation of the problem:

http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap

Take a look at the most popular answer starting "Let CondA ..."

farad · March 17, 2011

I did have a read did not want to be rude and I will read it again and again so I understand the concept. It just went over my head the first time.

I can understand that the concept will be the same even though we are working with time stamps and dates have some similar attributed.

I think I will give it another shot, if not then just stick to what I have.

thanks.

btherl · March 17, 2011

No worries, it's a bit of a confusing concept. Just think about the possibilities:

1. Booking A is completely before Booking B (End of booking A <= Start of booking B)

2. Booking B is completely before booking A (End of booking B <= Start of booking A)

3. ???

What is possiblity 3? Well if one booking is not entirely before or after the other, then they must overlap. It doesn't matter how they overlap - you already know there is a conflict, and you can report it back to the user. So you just need to check those two conditions:

If End of booking A <= Start of booking B OR End of booking B <= Start of booking A, then the bookings do not overlap.

Note that that is "OR", not "AND".

farad · March 17, 2011

Hi I will give that a try. Thank you for your kindness it is much appreciated and your thorough explanation. I can understand alot of individuals may come to this forum to find that elusive answer and then banish never to be seen. Hopefully as my knowledge developed I will contribute something back to this forum.

farad · March 23, 2011

Hi

I though I had it working but after several testings. There are some flaws in it

This the original code I used and modified based on the requirement.

$sql =" Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'

AND (End_Time_<='$End_Time'

OR '$End_Time' <= Start_Time_)

";

Now the above code works when I test certain times.

I generally input 13:00 - 14:00.

Now if I input 13:00 - 14:00 again it will block it, as it is a double booking.

If I put 12:00 14:00 it will block it which is good.

However if the first booking is 09:00 - 17:00 then I make a booking of 13:00 - 14:00 it will allow it.

The above block certain double bookings but not all

I also tried your suggestion

$sql =" Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'

AND (End_Time_ <= '$Start_Time'

OR '$End_Time' <= Start_Time_)

The biggest problem I am having is if a room is already booked for 09 - 17 and you do 13 - 14 it allows it. This can get a little frustrating. I hope someone can help

farad · March 23, 2011

Any suggestionss

btherl · March 23, 2011

Period A: 09 - 17

Period B: 13 - 14

End of booking A (17) <= Start of booking B (13) is false

End of booking B (14) <= Start of booking A (09) is false

Therefore, the bookings overlap.

So the algorithm is fine, but the implementation is not working. The code you have looks good here:

$sql ="   Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'
AND (End_Time_ <= '$Start_Time' 
OR '$End_Time' <= Start_Time_)";

The database is booking A and the new booking is booking B. So that condition is EndA <= StartB OR EndB <= StartA, which is the right condition.

Can you post here the query which gives the incorrect results, as well as the row from the database which it is supposed to conflict with?

farad · March 24, 2011

Hi, thanks, for taking your previous time, answering my questions.

I tried that code as well, weirdly enogh it allows double booking. If you for example book:

A: 09 - 17

then

B: 13 - 14 (Allows this )

I thought perhaps if I pasted some more codes of mine, then maybe we can demistify it, or perhaps maybe not.

The query used to create the table, this is only one table taken from the database, as the time stamps are stored here.

mysql_query("Create table tModule

(

Module_ID_ VARCHAR(100) NOT NULL,
Module_Title_ VARCHAR(150) NOT NULL,
Year_ INT(100) NOT NULL,
Semester_ VARCHAR(30) NOT NULL,
Credits_ INT(10) NOT NULL,
Day_ VARCHAR(20) NOT NULL,
Start_Time_ TIME NOT NULL,
End_Time_  TIME NOT NULL,
Tutor_ VARCHAR(20) NOT NULL,
Room_ VARCHAR(50) NOT NULL,
Notes_ VARCHAR(200),

PRIMARY KEY (Module_ID_)
);
");
The above definitely works as I can check the table in phpmyadmin.

$sql ="	Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'
AND (End_Time_ <= '$Start_Time' OR '$End_Time' <= Start_Time_)";
$result = mysql_query($sql, $odbc) or die ("<h2>Can't run query11</h2>");

f(mysql_num_rows($result)==0)
{
$sql ="INSERT INTO tModule (Module_ID_, Module_Title_,Year_, Semester_, Credits_, Day_, Start_Time_, End_Time_, Tutor_, Room_, Notes_)
VALUES ('$Module_ID', '$Module_Title', '$Year', '$Semester', '$Credits', '$Day', '$Start_Time', '$End_Time', '$Tutor', '$Room', '$Notes')";
//This check if anything is returned from the previous query, if so, then it will not insert, but if nothing is returned meaning that time slot is not taking, the data is added.

btherl · March 24, 2011

Do you have more than one booking on that day? Because that query will only tell you if ANY booking on that day does not overlap, not if ALL bookings on that day do not overlap.

If that's not the case, can you please post the output of this code:

$sql = "SELECT * FROM tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'";
$result = mysql_query($sql, $odbc) or die ("<h2>Can't run query11</h2>");
while ($row = mysql_fetch_array($result)) {
  print "<pre>"; var_dump($row); print "</pre>";
}

$sql ="	Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'
AND (End_Time_ <= '$Start_Time' OR '$End_Time' <= Start_Time_)";
print "Using query $sql<br>";
$result = mysql_query($sql, $odbc) or die ("<h2>Can't run query11</h2>");
while ($row = mysql_fetch_array($result)) {
  print "<pre>"; var_dump($row); print "</pre>";
}

What I would like to see from that code is all bookings for that day, the SQL being executed after the variables are substituted, and the bookings which were found to be not overlapping.

farad · March 24, 2011

There can be several bookings for a room in one day, that is how I want to design it.

array(22) {
  [0]=>
  string(5) "121aa"
  ["Module_ID_"]=>
  string(5) "121aa"
  [1]=>
  string(50) "Level 1 Adventures in Criminology: Intro and Scope"
  ["Module_Title_"]=>
  string(50) "Level 1 Adventures in Criminology: Intro and Scope"
  [2]=>
  string(1) "1"
  ["Year_"]=>
  string(1) "1"
  [3]=>
  string(10) "Semester 1"
  ["Semester_"]=>
  string(10) "Semester 1"
  [4]=>
  string(2) "15"
  ["Credits_"]=>
  string(2) "15"
  [5]=>
  string(6) "Monday"
  ["Day_"]=>
  string(6) "Monday"
  [6]=>
  string( "13:00:00"
  ["Start_Time_"]=>
  string( "13:00:00"
  [7]=>
  string( "14:00:00"
  ["End_Time_"]=>
  string( "14:00:00"
  [8]=>
  string(3) "121"
  ["Tutor_"]=>
  string(3) "121"
  [9]=>
  string(6) "UCP005"
  ["Room_"]=>
  string(6) "UCP005"
  [10]=>
  string(0) ""
  ["Notes_"]=>
  string(0) ""
}

array(22) {
  [0]=>
  string(3) "211"
  ["Module_ID_"]=>
  string(3) "211"
  [1]=>
  string(30) "Level 1 Western Civilisation 1"
  ["Module_Title_"]=>
  string(30) "Level 1 Western Civilisation 1"
  [2]=>
  string(1) "1"
  ["Year_"]=>
  string(1) "1"
  [3]=>
  string(10) "Semester 1"
  ["Semester_"]=>
  string(10) "Semester 1"
  [4]=>
  string(2) "15"
  ["Credits_"]=>
  string(2) "15"
  [5]=>
  string(6) "Monday"
  ["Day_"]=>
  string(6) "Monday"
  [6]=>
  string( "09:00:00"
  ["Start_Time_"]=>
  string( "09:00:00"
  [7]=>
  string( "17:00:00"
  ["End_Time_"]=>
  string( "17:00:00"
  [8]=>
  string(3) "121"
  ["Tutor_"]=>
  string(3) "121"
  [9]=>
  string(6) "UCP005"
  ["Room_"]=>
  string(6) "UCP005"
  [10]=>
  string(0) ""
  ["Notes_"]=>
  string(0) ""
}

Using query Select * from tModule where Semester_ = 'Semester 1' AND Day_ ='Monday' AND Room_ ='UCP005'AND (End_Time_ <= '15:00:00' OR '16:00:00' <= Start_Time_)

array(22) {
  [0]=>
  string(5) "121aa"
  ["Module_ID_"]=>
  string(5) "121aa"
  [1]=>
  string(50) "Level 1 Adventures in Criminology: Intro and Scope"
  ["Module_Title_"]=>
  string(50) "Level 1 Adventures in Criminology: Intro and Scope"
  [2]=>
  string(1) "1"
  ["Year_"]=>
  string(1) "1"
  [3]=>
  string(10) "Semester 1"
  ["Semester_"]=>
  string(10) "Semester 1"
  [4]=>
  string(2) "15"
  ["Credits_"]=>
  string(2) "15"
  [5]=>
  string(6) "Monday"
  ["Day_"]=>
  string(6) "Monday"
  [6]=>
  string( "13:00:00"
  ["Start_Time_"]=>
  string( "13:00:00"
  [7]=>
  string( "14:00:00"
  ["End_Time_"]=>
  string( "14:00:00"
  [8]=>
  string(3) "121"
  ["Tutor_"]=>
  string(3) "121"
  [9]=>
  string(6) "UCP005"
  ["Room_"]=>
  string(6) "UCP005"
  [10]=>
  string(0) ""
  ["Notes_"]=>
  string(0) ""
}

btherl · March 24, 2011

Ok, that is the problem then. That query returns all bookings which DON'T overlap with the new booking. What you need instead is all bookings that DO overlap. So the query needs to be reversed, by adding "NOT":

$sql ="	Select * from tModule where Semester_ = '$Semester' AND Day_ ='$Day' AND Room_ ='$Room'
AND NOT (End_Time_ <= '$Start_Time' OR '$End_Time' <= Start_Time_)";
$result = mysql_query($sql, $odbc) or die ("<h2>Can't run query11</h2>");

if (mysql_num_rows($result) == 0) {
  # There are no overlapping bookings
}

The old query returned the booking from 13-14, saying that it does not overlap with 15-16. That doesn't tell you anything useful because there might be other overlapping bookings. The new query will return the booking from 09-17, telling you there is a conflict. Then you can reject the new booking and optionally tell the user which booking it clashes with.

farad · March 24, 2011

Hey again. I will try that. Thanks alot. I am keen on learning more about databases (even though I do hardware support) and I find that the key to succeeding in anything and fully grasping any core subject is to understand the fundamentals of it, or shall I say the concept.

You seem to know the logic behind every structure and although I do break things down into manageable steps which is a common methodology used in troubleshooting key components of any machine, and cancelling out devices one by one. I wanted if you could recommend a book, because, obviously your knowledge is superior and thus will predominantly specify the best book.

Like I said, I will start using this forum more often. It was not a necessity to work, and even if for some strange reason it does not function, I still appreciate the effort.

PS: off topic, you a cricket fan

thanks

btherl · March 24, 2011

I'm australian, but not really a cricket fan.. I am still happy when our team wins though

Breaking things down into manageable steps is the main principle of programming, I believe. OOP, MVC, SQL, all these concepts are ways to break down and simplify complex ideas. But there are so many ways to break things down, and that's where the difficulty is. If you break things down the wrong way you'll end up with trouble later. It's only experience which will tell you what the right way to break it down is. Eg Model-View-Controller is a great way to break things down, but the decision of what goes in which part is often tricky. And each of the parts of MVC need to be broken down further into functions, objects, data structures, and a few well-chosen global variables.

As for book recommendations, I would instead suggest saving up and doing the Landmark Forum. It did more than just improve my programming, it totally changed the way I view the world. And I don't have a single book on programming on my desk here - just some red bull, an empty cup of coffee and years of experience in making mistakes

farad · March 24, 2011

Hey

That is awesome

I personally help quite abit on the Malware side and troubleshooting on other forums, but we can always develop our skills into others areas. I truly understand what you mean, as the majority of problem you encounter are primarily real-world based situations and thus you know the rest ...

I will the points on board .

If you don't mind I might add you on facebook, however if you use your facebook primarily for faimly etc. then I understand.

btherl · March 24, 2011

You're welcome to add me on facebook, make sure to add a message to the friend request though.

Sign In

Time query help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information