php mysql 考勤系统,使用PHP&的考勤系统的MySQL

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).