Get Clashing bookings

[fife]

Merry Christmas All

 

Hope everyone here is very well today.

 

ok so I have a booking system where I store the id of the client, idstylist, date, start time and end time of a particular booking.  Im trying to write a query that runs when placing a new booking.  It basically checks that the stylist is not already busy with another client.  I have a working query below

 

 

$query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND  bookingDate=%s AND idstylist=%s) AND  (%s BETWEEN sTime AND eTime)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"), $this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query);

 

The above query works however there is a flaw.  Lets say the booking ends a 10:30:00 and I want to book the client in at 10:30:00 for another treatment the query says no.  So I changed the query so it now looks like this....

 

$query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND  bookingDate=%s AND idstylist=%s) AND  (sTime>=%s AND eTime<%s)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"), $this->db->GetSQLValueString($stime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query);

 

Now when I try to make a booking if the stylist has any other bookings whatsoever that day it says there is a clash and doesn't allow me to complete the form.

 

Can someone please help me write a query that will just check if the stylist is already on a job at the $stime of the booking or show me where I've gone wrong with the queries? 

 

in the second query between the sTime and eTime I've tried AND , OR and neither work

 

[Barand]

If you have a new booking (time_start to time_end) then any other bookings where

(bookingend > time_start) && (bookingstart < time_end)

will clash with the new one

[fife]

Barand I totally wish I had your experience. lol only another 30 years to go.  Can you please explain further?

[Barand]

This may help. New booking is from start to end. Existing bookings are from s to e

                          new booking
                            |         |
                          start      end
   -------------------------------------------------------------------                       
                            |         |
                     s--------e       |                 clashing
                            |  s----e |
                            |    s-----------e          e > start
                         s---------------e                 &&
                            |         |                  s < end
                            |         |
   --------------------------------------------------------------------
                            |         |                    
           s------------e   |         |                         OK
                            |         | s----------e            OK
                            |         |
                            

[fife]

OK I see my flaw.  So I need to also parse the end time of the booking im trying to make so I changed my query to

$query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND  bookingDate=%s AND idstylist=%s) AND   (%s > bookingSTime) AND (%s < bookingETime)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query);

 

however this still allow a booking to be booked that should of clashed.

[fife]

ok so maybe I wrote that wrong.  I just tried this

 

$query = sprintf("SELECT ib FROM table WHERE (sys=%s AND bran=%s AND  bookingDate=%s AND idstylist=%s) AND   (bookingETime=%s > bookingSTime=%s) AND (bookingSTime=%s < bookingETime=%s)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text") ,$this->db->GetSQLValueString($stime, "text"),$this->db->GetSQLValueString($etime, "text")  );$result = $this->db->query($query);

 

but that didnt work eother and still allowed the bookings

[fife]

ok I definitely know I wrote that last one wrong.  I just cant get how to write the query at all

[Barand]

Suppose we have

mysql> select * from appointment;
+---------------+----------+-----------+------------+-----------+----------+
| idappointment | idclient | idstylist | app_date   | app_start | app_end  |
+---------------+----------+-----------+------------+-----------+----------+
|             1 |        1 |         1 | 2014-12-18 | 10:00:00  | 10:45:00 |
|             3 |        2 |         1 | 2014-12-18 | 12:00:00  | 12:45:00 |
|             4 |        3 |         2 | 2014-12-18 | 10:00:00  | 10:45:00 |
|             5 |        4 |         1 | 2014-12-18 | 13:00:00  | 13:45:00 |
|             6 |        5 |         2 | 2014-12-18 | 16:00:00  | 16:45:00 |
+---------------+----------+-----------+------------+-----------+----------+

and client wants to book 2014-12-18 from 12:45 to 13:30

SELECT s.idstylist
 , s.name
 , COUNT(idappointment) as clashes
FROM stylist s
    LEFT JOIN appointment a
        ON s.idstylist = a.idstylist
        AND app_date = '2014-12-18'
        AND app_start < '13:30' AND app_end > '12:45'
GROUP BY idstylist;

which gives this

+-----------+------+---------+
| idstylist | name | clashes |
+-----------+------+---------+
|         1 | John |       1 |
|         2 | Jane |       0 |
+-----------+------+---------+

showing that Jane is free for that booking

Edited December 16, 2014 by Barand

[fife]

ha.  I see it.  Had to walk away but I finally get it.  Updated my query and it didn't work.  I could see there was nothing wrong with the query so went looking for my error.  I then noticed that my start time was in 24 hr clock and my end time was in 12hr clock.  As soon as I fixed that it worked.  Thank you very much Barand  Awesome answer as usual. I had to write it out on paper several times before i saw what you were talking about.

 

We'd all be in big trouble without people like you on this forum.  Again thank you very much for your time.  I'm going to have to start sending you money at this rate

 

 

Query now looks as follows and works perfectly

 

public function checkClashStylist($bdate, $stime,$etime, $stylist){$query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND  bookingDate=%s AND idstylist=%s) AND   (stime < %s) AND (etime > %s)",//  1:30 >$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query);if($result  && $this->db->num_rows($result) > 0){//there is a clashreturn true; }//no clash carry onreturn false;  }