I have a form which when it is submitted, it will output the data inputted into the form and output it in the query result. If a textbox is empty it searches all the data for that particular field. At the moment my query is outputting this:
SessionId ModuleId CourseId ClassId StudentUsername TeacherUsername Grade
ABB CHT2520 INFO101 104 u0867587 m.prigmore A
ABB CHI2550 INFO101 101 u0867587 j.lu A
ABB CHI2550 INFO101 104 u1231231 j.lu F
ABB CHT2520 INFO101 101 u1231231 m.prigmore F
NVK CHI2550 INFO101 101 u0867587 j.lu F
RMI CHI2565 INFO101 102 u0660663 j.forden F
It is outputting more rows then it should do. One student belongs in one class for one module. The problem is that it is showing that one student belongs in two classes for one module which is incorrect. The reason it is doing this is because it recognises which class a student is in depending on the module the student is taking.
So if there are two class for module 'CHI2550', any student that takes module 'CHI2550' are allocated to both classes when it should be one student is allocated to one class for one module and that should be determined by the 'StudentClass' table. Problem is that if I include 'StudentClass' in one of the JOINS then the query outputs 9 rows.
The query should of outputted this:
SessionId ModuleId CourseId ClassId StudentUsername TeacherUsername Grade
ABB CHI2550 INFO101 101 u0867587 j.lu A
ABB CHI2550 INFO101 104 u1231231 j.lu F
NVK CHT2520 INFO101 103 u0867587 m.prigmore F
RMI CHI2565 INFO101 102 u0660663 j.forden F
How can I get the query to output the result above? Below is the query:
SELECT * FROM Course c
INNER JOIN CourseModule cm ON c.CourseId = cm.CourseId
JOIN Module m ON cm.ModuleId = m.ModuleId
JOIN Class cl ON m.ModuleId = cl.ModuleId
JOIN Teacher t ON cl.TeacherId = t.TeacherId
JOIN Session s ON m.ModuleId = s.ModuleId
JOIN Grade_Report gr ON s.SessionId = gr.SessionId
JOIN Student st ON gr.StudentId = st.StudentId
WHERE
('".mysql_real_escape_string($sessionid)."' = '' OR gr.SessionId = '".mysql_real_escape_string($sessionid)."')
AND
('".mysql_real_escape_string($moduleid)."' = '' OR s.ModuleId = '".mysql_real_escape_string($moduleid)."')
AND
('".mysql_real_escape_string($courseid)."' = '' OR c.CourseId = '".mysql_real_escape_string($courseid)."')
AND
('".mysql_real_escape_string($classid)."' = '' OR cl.ClassId = '".mysql_real_escape_string($classid)."')
AND
('".mysql_real_escape_string($teacherid)."' = '' OR t.TeacherUsername = '".mysql_real_escape_string($teacherid)."')
AND
('".mysql_real_escape_string($studentid)."' = '' OR st.StudentUsername = '".mysql_real_escape_string($studentid)."')
AND
('".mysql_real_escape_string($year)."' = '' OR st.Year = '".mysql_real_escape_string($year)."')
AND
('".mysql_real_escape_string($grade)."' = '' OR gr.Grade = '".mysql_real_escape_string($grade)."')
Below are each table and their data (most important tables for the problem I am facing are StudentClass, Class, Student, Module):
Course Table:
CourseId CourseName Duration
INFO101 Bsc Information Communication Technology (ICT) 3/4
CourseModule Table:
CourseId ModuleId
INFO101 CHI2550
INFO101 CHI2565
INFO101 CHT2520
Module Table:
ModuleId ModuleName Credits
CHT2520 Advanced Web Programming 20
CHI2565 E-Commerce Business and Technology 20
CHI2550 Modern Database Applications 20
Session Table:
SessionId ModuleId Semester SessionDate SessionTime TeacherId Room Building
NVQ CHT2520 Spring 2011-03-21 13:00:00 T2 CW2/04 Canalside West
NVK CHT2520 Fall 2011-11-10 10:00:00 T2 CW2/04 Canalside West
RMI CHI2565 Fall 2011-09-13 12:00:00 T5 CW5/01 Canalside West
RMT CHI2565 Spring 2011-03-29 14:00:00 T2 CW2/04 Canalside West
ABB CHI2550 Spring 2011-03-15 12:00:00 T1 CW4/10 Canalside West
ABH CHI2550 Summer 2011-05-03 15:00:00 T4 CW4/10 Canalside West
Grade_Report Table:
StudentId SessionId Grade Mark
S1 NVK F 5
S2 ABB A 80
S1 ABB A 80
S3 RMI F 0
Student Table:
StudentId StudentForename StudentSurname Year StudentUsername CourseId
S1 Mayur Patel 3 u0867587 INFO101
S2 Jim Carlton 3 u1231231 INFO101
S3 Ahmed Seedat 3 u0660663 INFO101
S4 Amar Barot 3 u0954857 INFO101
S5 Richard Davies 3 u0877223 INFO101
StudentClass Table:
ClassId StudentId
101 S1
102 S3
103 S1
104 S2
Class Table:
ClassId Room ClassDay ClassTime ModuleId CourseId TeacherId
101 CW4/10 Thursday 10:00:00 CHI2550 INFO101 T1
102 CW5/01 Wednesday 12:00:00 CHI2565 INFO101 T5
103 CW2/04 Monday 15:00:00 CHT2520 INFO101 T2
104 CW4/10 Thursday 11:00:00 CHI2550 INFO101 T1
Teacher Table:
TeacherId TeacherForename TeacherSurname TeacherUsername TeacherPassword
T1 Joan Lu j.lu scomp21
T2 Martyn Prigmore m.prigmore prigmore36
T3 Arshard Ali a.ali aliict
T4 Paul Judge p.judge data01
T5 John Forden j.forden hudds_10
