retrieve data from database query

See more:

PHP

MySQL

All-Topics

DateTime

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