douzhang1926 2019-06-03 23:34
浏览 167
已采纳

从select语句中查找重叠时间

Okay, so I have a table (timetable) in a database (calendar) which if formatted like so:

+----+--------+-----------+-----------+---------+
| id |  name  |    day    | startTime | endTime |
+----+--------+-----------+-----------+---------+
|  1 | George | Sunday    | 12:00     | 14:00   |
| 2  | Dan    | Monday    | 13:30     | 15:30   |
|  3 | Jeff   | Wednesday | 12:00     | 14:00   |
|  4 | Bill   | Monday    | 13:45     | 15:45   |
+----+--------+-----------+-----------+---------+

Then I have some PHP as follows:

  <?php
 $sql = "SELECT * FROM timetable WHERE id IN (1, 2, 3, 4)"
    $result = $con->query($sql);
     while ($row = mysqli_fetch_assoc($result)) {
         $array2[] = $row;
    }    

  echo json_encode($array2, JSON_PRETTY_PRINT);

  ?>

And that outputs the entire table, as an associated array in JSON format.

I want it to only output the two that clash. There will never be more than two that clash in the database due to the input method, so it only needs to check if there's one clash, and output the JSON for both rows.

I'm not sure where to start. Is it better to do it programatically via PHP, or is there a way to do with with mysql? I was thinking if it's done programatically, maybe two nested for loops and if statements for greater than or less than on the time fields? But seems messy, and I'm thinking there's likely a more clever end elegant solution that I can't think of.

Thank you for any help you can provide.

  • 写回答

1条回答 默认 最新

  • doupian9490 2019-06-03 23:55
    关注

    You can do it with JOIN in SQL like below:-

    To find "any overlap", you compare the opposite ends of the timeframe with each other.

    SELECT * FROM timetable a
    JOIN timetable b 
        on a.starttime <= b.endtime
        and a.endtime >= b.starttime
        and a.name != b.name;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 我知道什么是混合树,但是怎么写代码啊
  • ¥50 开发板linux系统安装dpkg,apt函数库 有偿
  • ¥15 浏览器时间循环 交互事件和延时事件的 优先级与执行问题
  • ¥15 GD模块安装出错,libgd无法正常安装
  • ¥20 求有缘人帮我把笛卡尔坐标系转换为经纬度 有偿
  • ¥15 vue2中使用计算属性
  • ¥50 远程桌面打开Mastercam、没有许可证、物理机打开正常
  • ¥15 ubuntu安装gdal后java读取tif文件报错
  • ¥15 请问lammps怎么做两种金属连接的原子浓度分布图
  • ¥15 求jacquard数据集