使用PHP&的考勤系统的MySQL [英] Attendance system using PHP & MySQL
问题描述
我的一个模块项目中有一个使用PHP和MySQL的出勤系统，MySQL表如下所示:
现在，时间表用于存储某节一天中某节课的时间表，并为其分配了一位老师. student_info 表包含有关学生及其所属部分的常规信息. 出勤表用于使用时间和学生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内部联接，但没有达到我想要的方式.
我当时认为可以从以下查询中输出第一行:
SELECT timeid
FROM timetable
WHERE flag > ?
AND semester = ?
AND section = ?
AND timeid BETWEEN ? AND ?
[更新]找到了一种解决方法，作为答案，不知道这是否是正确的方法.
在此先感谢您的帮助.
我的回答可能有点晚了，但是我也玩了一会儿，想出了一个在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 (出席人数表有仅在前几个日期被填充).
I have an Attendance system in one of my module project using PHP and MySQL, the MySQL table looks something like this:
Now, timetable is used to store the Time Table for classes in a day for a section and which teacher is assigned to it. The student_info table contains general information about students and the section they belong to. The attendancetable is used to record those who are absent using time and student id as primary key.
I could get a count of how many classes were taken in a semester as follows:
SELECT count(*) as total FROM timetable WHERE flag > ? AND semester = ? AND section = ? AND timeid BETWEEN ? AND ?
Then computed how many times a student attended and also calculate the percentage of attendance.
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
Now, I want to also display a kind of attendance sheet as follows:
I tried SQL Inner Join but didn't get the way I wanted.
I was thinking that the first row can be output form the following query:
SELECT timeid
FROM timetable
WHERE flag > ?
AND semester = ?
AND section = ?
AND timeid BETWEEN ? AND ?
[UPDATE] Found a way to do this, given as an answer, don't know if that's the right way.
Thank You in advance for helping.
I am probably a bit late with my answer, but I have been playing around a bit too and came up with a solution that is largely done within MySql: my solution builds a dynamic SELECT statement that is executed at the end to provide me with the table, having columns spanning a predefined era:
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;
What still needs to be done is the translation of date Ids in the column headings to proper dates and, likewise, the translation of student Ids into names or univ_roll numbers.
i have also taken the liberty of changing the table layout a little bit: the primary key in the timetable is now just an integer, the actual time is stored separately in another column, providing flexibility in case dates or times might change.
Here is a little rextester demo: http://rextester.com/LPFF99061 (the attendance table has only been filled for the first few dates).
这篇关于使用PHP&的考勤系统的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
