[SOLVED] mysql BETWEEN

[paul2463]

Hi Guys

 

what probably would come down to a simple problem but I cant get my head around the coding, I am making a simple PHP/MYSql program for bookings for the simulator I instruct on, the bookings table has a date column (simple Date format)a start time column (hh:m:ss)and an end time column (hh:mm:ss). to make a unique booking I need to check the database to see if a booking for any of the times has been taken(i.e. non unique)

//$st is the column start time
//$et is the column end time
//$nst is the new start time for a booking I wish to make
//$net is the new end time for the booking I wish to make

$query = "SELECT idbookings FROM bookings WHERE '$nst' NOT BETWEEN('$st','$et') AND `date` = some date";
$result = mysql_query($query) or die ("Error in query - $query" . mysql_error());
     if (mysql_num_rows($result)<1)
     {
     $query1 = "SELECT idbookings FROM bookings WHERE '$net' NOT BETWEEN('$st','$et')AND `date` = some date";
     $result1 = mysql_query($query1) or die ("Error in query - $query1" . mysql_error());
     if (mysql_num_rows($result1)<1)
          {
           $query2 = "make booking";
          }
     }
     else
     {
     echo " a booking is already made for that date and times overlap";
     }

 

is this the best way to do it or is there a faster way??

 

many thanks in advance

[obsidian]

BETWEEN is used with an AND in the clause:

SELECT idbookings 
FROM bookings 
WHERE ('$nst' NOT BETWEEN '$st' AND '$et') 
AND `date` = some_date;

[paul2463]

does this make sense as a query?

$query = "SELECT * FROM bookings WHERE ((BETWEEN '$nst' AND '$net') IN (BETWEEN '$st' AND '$et'))  AND `date` = some_date";

// select everything from bookings where all times between the new start and end time IS IN the list of all times between the
// booked start and end times on a certain date

[obsidian]

does this make sense as a query?

 

No. What are you trying to accomplish with the IN?

[paul2463]

obs

thanks for the reply

I was just thinking that between would bring out all the times between the start time and end time and the IN would check that the new times were In the already booked times.

 

I have just figured the flaw in my thinking about the whole thing, I tried my queries and get an unknown column error because the input variables are in the place of the column name in the WHERE clause. I need to pass the query a new start time and end time then query the database for any bookings that encompass that time for instance

 

idbook    bdate            stime            etime

1            2007-03-09    09:00:00      10:30:00

2            2007-03-09    12:00:00      13:30:00

 

if I wish to make a new booking between 10:00:00 and 11:30:00 this would throw an error because it conflicts with a booking already made, can i do this with a query or do I have to pull all bookings for a certain date into PHP and write functions to check?

[obsidian]

Here is a small flaw in  your thinking: let's say I want to make an appointment from 3 PM to 5PM. Simply checking that my start and end times don't overlap with an existing appointment isn't enough: what happens if you have an appointment from 4-4:30? Neither the start nor end times overlap with the other, so it won't get flagged, even though there is a clear conflict. The same exceptions can occur when you're trying to insert a short time and it happens to fall within a large existing appointment. The easiest way I've found to do this sort of thing is to actually break your day into X number of time slots (I like to use 15 minute segments). Then, you simply loop through all the segments that the new appointment will take up and see if any of them are already booked.

 

If you are wanting to use the time method alone, you'll need to check two inverse comparisons: 1) make sure that neither the start nor end times of the new appointment fall within the time frame of another existing appointment and 2) make sure that neither the start nor end times of an existing appointment fall within the time frame of your new appointment. If both of these are passed you should be good to go. So, something like this should work:

 

SELECT * FROM myBookings
-- Check the booking date first
WHERE bdate = '$ndate'
-- Check the existing appointment times next
AND ((stime BETWEEN '$nst' AND '$net' OR etime BETWEEN '$nst' AND '$net')
-- Check the new appointment times finally
OR ('$nst' BETWEEN stime AND etime OR '$net' BETWEEN stime AND etime))

 

I hope this helps you with your logic. If the above query returns a row, you have a conflict.

[paul2463]

Obsidian

 

many many many thanks

 

this is just a fun project for work as anything would be better than the rubbish that got provided with the simulator

 

but that was the biggest sticking point, this query works perfectly and thanks for the logic behind it, i had missed that fact

[obsidian]

this is just a fun project for work as anything would be better than the rubbish that got provided with the simulator

 

but that was the biggest sticking point, this query works perfectly and thanks for the logic behind it, i had missed that fact

 

Glad to help. I know the frustration with the date and time ranges. I had the same holdup with the calendar I wrote for my current employer. It took me forever and a day to remember all the little nuances that could cause overlaps with ranges in dates. Once you've got the concept, it can easily be modified to whatever you need.

 

Good luck!