php - 如何比较同一天的多个日程和出勤情况

Question

对于这个微不足道的问题，我深表歉意，我花了很多时间在谷歌上搜索这个问题的答案。请帮忙..我有一个学生日程表和学生出勤表。我想要的是将学生的日程安排与出勤情况进行比较。

表表

ID | stud_id | day   | timein  | timeout | schedule_hour |
1  | 8888    | Monday| 08:00AM | 09:00AM |   1           |
2  | 8888    | Monday| 10:00AM | 12:00PM |   2           |
3  | 8888    | Monday| 01:00PM | 03:00PM |   2           |
4  | 8888    |Tuesday| 07:00AM | 09:00AM |   2           |  
5  | 8888    |Tuesday| 03:00PM | 04:00PM |   1           |
6  | 8888    |Tuesday| 06:00PM | 08:00PM |   2           |

出席人数

ID |  stud_id | day   | timein  | timeout |  hour |
 1  | 8888    | Monday| 07:55AM | 09:10AM |1.15   |
 2  | 8888    | Monday| 09:51AM | 12:03PM |2.12   |
 3  | 8888    | Monday| 12:50PM | 03:05PM |2.15   |
 4  | 8888    |Tuesday| 06:53AM | 09:15AM |2.22   | 
 5  | 8888    |Tuesday| 02:50PM | 04:06PM |1.16   |
 6  | 8888    |Tuesday| 05:52PM | 08:09PM |2.17   |
 7  | 8888    |Tuesday| 09:52PM | 10:09PM |1.17   |

php 代码如下:-

<?php
$query=$conn->query("SELECT *
FROM student a,attendance d,schedule e
WHERE a.student_id = d.student_id
AND d.student_id = e.student_id
AND a.student_id = $get_id")or die(mysql_error());
$totalhour = 0;
$totalitems = 0;

function hourandmin($time1){
$hour = 0;
$minute = 0;
while($time1 > 1.0){
$hour = $hour + 1;
$time1 = $time1 - 1;
}

if($time1 < 1.0){
$minute = $time1 * 60;
}

$time1 = round($hour).".".round($minute)."";
return $time1;
}

while($row = $query->fetch()){

$date = strtotime($row['login']);
$date2 = strtotime($row['logout']);
$hour = ($date2 - $date) / (60*60);
if ($hour < 0){
$hour = 0;
}
?>
<tr>
<td><?php echo $row['student_id']; ?></td>
<td><?php echo $row['day']; ?></td>
<td><?php echo $row['timein']; ?></td>
<td><?php echo $row['timeout']; ?></td>
<td><?php echo  hourandmin((($hour))); ?></td>
<td><?php echo $schedule_hour; ?></td>





                                </tr>
                                    <?php  } ?>

我不知道如何输出计划时间例如，在出勤表第 7 行中，他计时了，但因为他没有该时间的时间表，所以输出是加类

ID | stud_id | day   | timein  | timeout | hour |schedule_hour
1  | 8888    | Monday| 07:55AM | 09:10AM |  1.15| 1
2  | 8888    | Monday| 09:51AM | 12:03PM |  2.12| 2
3  | 8888    | Monday| 12:50PM | 03:05PM |  2.15| 2
4  | 8888    |Tuesday| 06:53AM | 09:15AM |  2.22| 2
5  | 8888    |Tuesday| 02:50PM | 04:06PM |  1.16| 1
6  | 8888    |Tuesday| 05:52PM | 08:09PM |  2.17| 2 
7  | 8888    |Tuesday| 09:52PM | 10:09PM |  1.17| overtime

Answer 1

希望这有帮助

SELECT tatt.stud_id, tatt.day, tatt.timein, tatt.timeout, tatt.hour,tsched.schedule_hour FROM table_attendance tatt INNER JOIN table_schedule tsched ON tsched.stud_id = tatt.stud_id AND tsched.day = tatt.day AND tatt.timein <= tsched.timein AND tatt.timeout > tsched.timeout;