基于PHP&MySQL的考勤系统

Question

我的一个模块项目中有一个考勤系统，使用PHP和MySQL,MySQL表如下所示：

现在，时间表被用来存储一天中某一段的类时间表，以及分配给哪位老师。student_info表包含有关学生及其所属部分的一般信息。attendancetable用于记录缺勤人员，使用时间和学生id作为主键。

我可以得到一个学期有多少类被录取的统计如下：

SELECT count(*) as total FROM timetable WHERE flag > ? AND semester = ? AND section = ? AND timeid BETWEEN ? AND ?

然后计算一个学生参加的次数，也计算出勤率。

SELECT stu.* , 
    (SELECT COUNT(*) 
    FROM attendancetable    att 
    WHERE att.s_id = stu.class_roll
    AND att.semester = ?
    AND att.timeid BETWEEN ? AND ? ) AS absent
FROM student_info   stu
WHERE stu.section = ?
AND stu.logYear = ?
AND stu.deleted = ?
ORDER BY stu.class_roll

现在，我还想显示一种考勤表，如下所示：

我尝试了SQL Inner Join，但没有得到我想要的方式。

我想第一行可以从以下查询输出：

SELECT timeid 
FROM timetable 
WHERE flag > ? 
  AND semester = ? 
  AND section = ? 
  AND timeid BETWEEN ? AND ?

[更新]找到了这样做的方法，给出了答案，不知道那是不是正确的方法。

事先谢谢你的帮助。

Answer 1

我不确定是否应该用这些更新来编辑问题。但这是我提出的部分解决方案。我会很高兴有人能帮助优化这个更多。

所以，我从这段代码开始。

SELECT timeid 
FROM timetable 
WHERE flag > ? 
  AND semester = ? 
  AND section = ? 
  AND timeid BETWEEN ? AND ?

从上面的代码，我可以得到日期的类采取。接下来，我检查一个学生是否在下面提到的日期出席或缺席：

SELECT attended FROM attendancetable
    WHERE s_id = ?
    AND semester = ?
    AND timeid = ?

有了这个，我就可以看到一个学生是否缺席了。在php中,它看起来如下所示：

//Getting the dates
$query = "SELECT timeid FROM timetable
        WHERE flag > ?
        AND semester = ?
        AND section = ?
        AND timeid BETWEEN ? AND ?";
$params = array(0, 1, 'A', '2017-01-01', '2017-06-30' );
$stmt = $db->prepare($query);
$stmt->execute($params);
$dates = $stmt->fetchall();

//checking if students is present or absent
$query = "SELECT attended FROM attendancetable
        WHERE s_id = ?
        AND semester = ?
        AND timeid = ?";

//Now I'm going to loop through the $dates
foreach($dates as $date){
    $params = array(1, 'A', $date['timeid']);
    $stmt = $db->prepare($query);
    $stmt->execute($params);
    $result = $stmt->fetchall();
    ($result) ? $present = 1 : $present = 0;
}

通过上面的方法，我能够计算出某一段的学生是否按照课程表出席/缺席某一节课。现在，如果我想要一节中的所有学生，我可以首先查询student_info表，然后为每个学生计算foreach循环

SELECT class_roll FROM student_info
    WHERE logYear = ?
    AND section = ?
    AND deleted = ? 
    ORDER BY class_roll LIMIT ?, ?

然后，每个学生都运行foreach循环，以检查他们是否缺席或在场。最后，我可以得到这个：

我在本地测试了大约200个学生的执行时间，当类只有39个时，执行时间大约为1.6秒。我也用以前的数据测试了一整个学期，代码超过30秒，触发了max_execution_time错误。到现在为止，我设定了每页25个学生数量的限制。

更新:max_execution_time错误似乎只在第一次运行时出现，但后来当我尝试重新生成它的not giving neyer错误时，完成任务所花的时间不到2秒。

Answer 2

我的答案可能有点晚了，但我也玩了一会儿，并提出了一个主要在MySQL中完成的解决方案：我的解决方案构建了一个动态select语句，该语句在结尾处执行，为我提供表，其列跨越预定义的纪元：

SET @str='SELECT sid';
SELECT @str:=concat(@str,', MAX(CASE tid WHEN ',t_id,
                    ' THEN \'OK\' ELSE \'\' END) d',t_id) 
--                            start date          end date
FROM times WHERE dati BETWEEN '20170810.0000' and '20171022.2359';
SET @str=concat(@str,' FROM att GROUP BY sid');
PREPARE qu FROM @str;
EXECUTE qu;

仍然需要做的是将列标题中的日期ID转换为适当的日期，同样地，将学生ID转换为姓名或univ_roll数字。

我还冒昧地稍微改变了表的布局：时间表中的主键现在只是一个整数，实际时间单独存储在另一列中，在日期或时间可能发生变化的情况下提供了灵活性。

这里有一个小小的rextester演示:http://rextester.com/lpff99061（出席表只填了前几个日期）。