3

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!

skaffman
  • 398,947
  • 96
  • 818
  • 769
tonoslfx
  • 3,422
  • 15
  • 65
  • 107

2 Answers2

2
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
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • hi Damir, thanks for answering. but i never used COALENSE function? Whats it? never used MAX in database? and for 'available', does it retrieve from the database? – tonoslfx Dec 22 '10 at 20:40
  • anyway, how do i echoing the database record to return the result? – tonoslfx Dec 22 '10 at 20:51
  • @boyee007, coalesce(Thera_A, 'available') means `IF Thera_A is not null then Thera_A else 'available'`. – Damir Sudarevic Dec 22 '10 at 21:02
  • @boyee007 Test this in smal steps, the sub-query first. Remove max() and group by and see result. Then add max() and group by. Then add outer query. It is easy if you watch results returned. – Damir Sudarevic Dec 22 '10 at 21:09
  • @boyee007, the SO is community-help site; so, keep you questions here -- some other people may benefit too. – Damir Sudarevic Dec 22 '10 at 21:17
1

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...

Dejan Marjanović
  • 19,244
  • 7
  • 52
  • 66