CODEWITHSUNDEEP
phpdatabase-design
tonoslfx
tonoslfx

Reputation: 3442

PHP Booking timeslot

Im developing a php booking system based on timeslot for daily basis. Ive set up 4 database tables!

  1. Bookslot (which store all the ids - id_bookslot, id_user, id_timeslot)
  2. Timeslot (store all the times on 15 minutes gap ex: 09:00, 09:15, 09:30, etc)
  3. Therapist (store all therapist details)

  4. User (store all the members detail)

    ID_BOOKSLOT ID_USER ID_THERAPIST ID_TIMESLOT

    1           10          1        1  (09:00)  
2           11          2        1  (09:00)  
3           12          3        2  (09:15)
4           15          3        1  (09:00)

Now, my issue is, it keep showing repeation for timeslot when i want echoing the data for example:

            thera a       thera b       thera c
  -------------------------------------------------
 09:00       BOOKED      available      available
 09:00     available       BOOKED       available
 09:00     available     available        BOOKED 
 09:15     available       BOOKED       available

as you can see, 09:00 showing three times, and i want something like below

           thera a       thera b       thera c
-------------------------------------------------
 09:00      BOOKED        BOOKED         BOOKED    
 09:15     available      BOOKED       available

There might be something wrong with joining the table or else. The code to join the table 

$mysqli->query("SELECT * FROM bookslot RIGHT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot LEFT JOIN therapist ON bookslot.id_therapist = therapist.id_therapist"

if anyone have the solution for this system, please help me out and i appriciate it much!

Upvotes: 3

Views: 4240

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

select 
      id_TimeSlot
    , coalesce(Thera_A, 'available') as Thera_A
    , coalesce(Thera_B, 'available') as Thera_B
    , coalesce(Thera_C, 'available') as Thera_C
from
(
    select
      t.id_TimeSlot
    , max(case b.id_Therapist when 1 then 'booked' else null end) as Thera_A
    , max(case b.id_Therapist when 2 then 'booked' else null end) as Thera_B
    , max(case b.id_Therapist when 3 then 'booked' else null end) as Thera_C
    from      TimeSlot  as t
    left join BookSlot  as b on b.id_TimeSlot  = t.id_TimeSlot
    left join Therapist as p on p.id_Therapist = b.id_Therapist
    group by  t.id_TimeSlot
) as xx ;

Test:

create table TimeSLot  (id_TimeSLot integer);
create table Therapist (id_Therapist integer);
create table BookSlot  (id_Therapist integer, id_TimeSlot integer);

insert into Therapist (id_Therapist)
values (1), (2), (3);

insert into TimeSlot (id_TimeSlot)
values (1), (2), (3), (4), (5);

insert into BookSlot (id_Therapist,id_TimeSlot)
values (1,1), (1,5), (2,1), (2,4), (3,1);

returns

id_TimeSlot  Thera_A    Thera_B    Thera_C
----------------------------------------------
1            booked     booked     booked
2            available  available  available
3            available  available  available
4            available  booked     available
5            booked     available  available

Upvotes: 2

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

I guess you need to GROUP BY id_timeslot, and then check which therapists are booked (or not).

To avoid complicated queries, make table "appointments" (id, u_id, t_id, start, stop, day)... You can then print appointments on particular day or timespan using BETWEEN start / stop and WHERE day = someday...

Upvotes: 1

Related Questions