从数据库查询中检索数据 [英] retrieve data from database query
问题描述
所以，我目前正在做一个小型的考勤系统.
我在构造必要的代码以符合我的需求时遇到问题.
因此，为了简短起见，我有一个名为"TAKE ATTENDANCE"的页面，用户可以在其中选择课程，时间开始，时间结束和组.
此信息将输入一个名为出席的表，其中包含:
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
然后，再次将学生插入表student_attendance中，从而为学生分配出勤:
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
知道如何执行SQL吗?我尝试了以下方法，尽管这会获取所有数据并且有点令人困惑:
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'
我需要什么SQL才能获得这种数据结构?我有点困惑.
帮助将不胜感激！
谢谢，
Francesco
What SQL do I need to get such data structure? I''m a bit confused.
Help would be much appreciated!
Thanks,
Francesco
推荐答案
课程' AND Attenance.att_periodID = '
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'
我需要什么SQL才能获得这种数据结构?我有点困惑.
帮助将不胜感激！
谢谢，
弗朗切斯科
What SQL do I need to get such data structure? I''m a bit confused.
Help would be much appreciated!
Thanks,
Francesco
这篇关于从数据库查询中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
