Databases
StackOverflow2013
Postsdbo
POquery structure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POquery structure
    primarykey
    data
    text
    <p>So, I'm currently doing a small attendance system.</p> <p>I have a problem structuring the necessary code to work with what I need.</p> <p>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.</p> <p>I have the following tables:</p> <pre><code>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; </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>or else to make things easier, I can remove the dates</p> <pre><code>Joe Borg 28 hours Nancy Ping 30 hours John Quin 27 hours Week 2 etc Week 3 etc Week 4 etc </code></pre> <p>Any idea how the SQL would be done? I tried the following though this gets all the data and is a bit confusing:</p> <pre><code>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'; </code></pre> <p>I also retrieved the course hours according to the course selected by using:</p> <pre><code>SELECT course_Hours FROM course WHERE course_ID = '$course'; </code></pre> <p>What SQL do I need to get such data structure? I'm a bit confused.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USoutis
    1. USNecron
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POquery structure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COProper [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.
      singulars
      1. POquery structure
      1. USoutis
    2. COThe 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.
      singulars
      1. POquery structure
      1. USoutis
    3. CO... 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`).
      singulars
      1. POquery structure
      1. USoutis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload