1

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.

I have the following tables:

CREATE TABLE period (
    periodID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    periodStart DATE NOT NULL, -- The start date for the period
    periodEnd DATE NOT NULL, -- The end date for the period
    period_Att DATETIME, -- When the attendance should be delivered - irrelevant for now
) Engine=InnoDB;

CREATE TABLE attendance (
    attID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
    att_courseID INT UNSIGNED NOT NULL,
    att_periodID INT UNSIGNED NOT NULL,
    lesson_start TIME NOT NULL,
    lesson_end TIME NOT NULL,
    att_duration TIME, --duration in time
    att_userID INT UNSIGNED NOT NULL,
    att_taken TINYINT(1) NOT NULL DEFAULT 0,
    FOREIGN KEY att_courseID REFERENCES course (courseID),
    FOREIGN KEY att_periodID REFERENCES period (periodID),
) Engine=InnoDB;

CREATE TABLE student_attendance (
    studentID INT UNSIGNED NOT NULL,
    attendanceID INT UNSIGNED NOT NULL,
    FOREIGN KEY studentID REFERENCES students (studentID),
    FOREIGN KEY attendanceID REFERENCES attendance (attendanceID),
) Engine=InnoDB;

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:

Week 1 - (30 hours of school hours per week)
1/1/2012      --- Joe Borg     4 hours (sum of hours is needed here)
              --- Nancy Ping   5 hours
              --- John Quin    5 hours
2/1/2012
3/1/2012
4/1/2012
5/1/2012
6/1/2012
7/1/2012

or else to make things easier, I can remove the dates

Joe Borg 28 hours
Nancy Ping 30 hours
John Quin 27 hours

Week 2
etc

Week 3
etc

Week 4
etc

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.

outis
  • 75,655
  • 22
  • 151
  • 221
Necron
  • 89
  • 10
  • Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. – outis Feb 24 '12 at 09:07
  • The sample schema is incomplete. Please provide the proper schema, along with sample data. Don't use [`SELECT *`](http://stackoverflow.com/questions/321299/) unless you're writing a DB administration program; select only the columns you need. [Implicit joins](http://stackoverflow.com/questions/44917/) shouldn't be used in favor of [explicit joins](http://stackoverflow.com/questions/2241991/). Don't interpolate values directly into statements. Instead, you should be using prepared statements. – outis Mar 26 '12 at 01:40
  • ... Prefixing column names with the table name they are a part of (e.g. `period.periodID`, but not `student_attendance.studentID`) is redundant; leave off the table name (e.g. `period.id`). – outis Mar 26 '12 at 01:41

0 Answers0