So, I'm currently doing a small attendance system.
I have a problem structuring the necessary code to work with what I need.
So, to make things short, I have a page named TAKE ATTENDANCE in which the user selects the course, time start, time end and group.
This information enters into a table named attendance which contains:
attID <br />
att_courseID (linked with the course table)<br />
att_periodID (linked with the period table)<br />
lesson_start <br />
lesson_end <br />
att_duration (duration in time)<br />
att_userID <br />
att_taken
Then again, the students are assigned to the attendance by being inserted in the table student_attendance:
studentID (student id linked to students table)<br />
attendanceID (attendance id linked to attendance table)<br />
sa_type (P or A - so that one knows whether the student was Present or Absent)
Now as for the period table, this table contains:
periodID (linked with the attendance period id)<br />
periodStart (The start date for the period)<br />
periodEnd (The end date for the period<br />
period_Att (When the attendance should be delivered - irrelevant for now)
What I want is that in the VIEW ATTENDANCE section, the user selects the course and period, and the attendance taken is displayed in the following format:
<br />
PERIOD SELECTED: 7 -> 3/3/2012 to 30/3/2012 (PERIODS ARE ALWAYS IN 4 WEEK INTERVALS)<br />
COURSE SELECTED: Degree in 3D Design<br />
<br />
Week 1 - (26 hours of school hours per week)<br />
8:00-9:00 9:00-10:00 10:00-11:00 11:00-12:00 12:30-13:30 13:30-14:30 14:30-15:30 15:30-16.30 16.30-17.30<br />
FRANCESCO DIMECH - MON: P P NA NA P P A NA NA<br />
FRANCESCO DIMECH - TUE: P P P NA P P NA NA NA <br />
FRANCESCO DIMECH - WED: P P NA NA P P A A NA<br />
FRANCESCO DIMECH - THUR: A P NA NA P P P NA NA<br />
FRANCESCO DIMECH - FRI: NA P P NA P P A NA NA<br />
<br />
Total Hours: 21/26 hours<br />
<br />
JOHN DEMICOLI - MON, TUE, etc<br />
<br />
Week 2<br />
bla bla<br />
<br />
Week 3<br />
bla bla<br />
<br />
Week 4<br />
bla bla<br />
<br />
NA - No Lesson<br />
P - Present<br />
A - Absent
Any idea how the SQL would be done? I tried the following though this gets all the data and is a bit confusing:
SELECT * FROM periods,attendance,student_attendance,students,course,stipend,users
WHERE attendance.att_periodID = periods.periodID
AND attendance.att_courseID = course.course_ID
AND attendance.att_userID = users.userid
AND student_attendance.attendanceID = attendance.attID
AND student_attendance.studentID = students.stud_ID
AND students.stud_StipendID = stipendID
AND attendance.att_courseID = '$course'
AND attendance.att_periodID = '$period'
I also retrieved the course hours according to the course selected by using:
SELECT course_Hours FROM course
WHERE course_ID = '$course'
What SQL do I need to get such data structure? I'm a bit confused.
Help would be much appreciated!
Thanks,
Francesco