PHP预订时段 [英] PHP Booking timeslot

问题描述

我们每天开发一个基于时间段的php预订系统。
Ive设置4个数据库表！

1. 书店（存储所有ids - id_bookslot，id_user，id_timeslot）


2. Timeslot（存储所有时间15分钟差距例如：09:00，09:15，09:30等）


3. 治疗师（存储所有治疗师详细信息）



4. 用户（存储所有成员详细信息）

   
   

   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）
     

   
   

   li>
   

现在，我的问题是，当我想要回显数据时，它会持续显示时区的重复：

  thera a thera b thera c 
 ------------------------ ------------------------- 
 09:00预订可用
 09:00可用预订
 09 ：00可用BOOKED 
 09:15可用预订
  

如您所见，09 ：00显示三次，我想要下面的东西

  thera a thera b thera c 
 ---- --------------------------------------------- 
 09 ：00预订已预订
 09:15可用预订
  

可能有问题加入桌子或其他。
加入表的代码

  $ mysqli-> query（SELECT * FROM bookslot RIGHT JOIN timeslot ON booklot.id_timeslot = timeslot.id_timeslot LEFT JOIN治疗师ON bookslot.id_therapist = therapist.id_therapist
  

如果有人有这个系统的解决方案，请帮助我，我很喜欢！

解决方案

  id_TimeSlot 
，合并（Thera_A，'available'）作为Thera_A 
，合并（Thera_B，'available'）为Thera_B 
，coalesce（Thera_C，'available'）作为Thera_C 
从
（
选择
 t.id_TimeSlot 
，max（case b.id_Therapist当1然后预订否则结束）作为Thera_A 
，max（case b.id_Therapist when then then'booked'else null end）as Thera_B 
，max（case b.id_Therapist when 3 then'booked'else null end）as The_Ca 
 from TimeSlot as t 
在b.id_TimeSlot = t.id_TimeSlot 
左边连接BookSlot b作为p on p.id_Therapist = b.id_Therapist 
 group by t.id_TimeSlot 
 xx; 
  

测试：

 code> create table TimeSLot（id_TimeSLot integer）; 
创建表治疗师（id_Therapist整数）; 
创建表BookSlot（id_Therapist integer，id_TimeSlot integer）; 
 
插入治疗师（id_Therapist）
值（1），（2），（3）; 
 
插入到TimeSlot（id_TimeSlot）
值（1），（2），（3），（4），（5）; 
 
插入BookSlot（id_Therapist，id_TimeSlot）
值（1,1），（1,5），（2,1），（2,4），（3,1） ; 
  

返回

  id_TimeSlot Thera_A Thera_B Thera_C 
 -------------------------------------- -------- 
 1已预订预订
 2可用可用
 3可用可用
 4可预订
 5已预订可用
  

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!

解决方案

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

这篇关于PHP预订时段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！