Hey guys. I've posted this a couple of times the past week or two and I have got some great ideas, yet for some reason, I cannot get the SQL statement correct... I'll try to explain this as best as I can.
I'm creating an online gradebook. Right now, I'm trying to add students (from tblStudents) to a class roster (add them to tblClassRoster). The fields in tblClassRoster are:
Class_ID KEY
Class_Section_ID
Class_Semester KEY
Class_Year KEY
Student_ID KEY
I want to have a select box that lists all of the students (from tblStudent) that are not currently in the selected class (I pass the Class ID, Section, Semester, and YEAR through the URL).
So, if I had ACC101, 2,SPRING,2002 as the selected class, and if tblStudent had:
123456 Sally Smith
111111 John Doe
222222 Mike Smith
and in tblClassRoster, there was:
111111 John Doe
for the selected class above, I want a SELECT box that will have:
123456 Sally Smith
222222 Mike Smith
The easiest way I've done this in the past is with subselects, but the version of MySQL on the machine does not support subselects. After receiving posts, and finding this code in a book I have, this is what I've come up with.
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S
LEFT JOIN tblClassRoster as CR
ON S.Student_ID = CR.Student_ID
WHERE CR.Class_ID = '$Class_ID_E'
AND CR.Class_Section_No = '$Class_Section_No_E'
AND CR.Class_Semester = '$Class_Semester_E' AND CR.Class_Year = '$Class_Year_E'
AND CR.Student_ID is null";
I hope that aligned alright. When I put this in, I get nothing. I don't get any errors, I just get an empty query. I've checked to make sure the names were correct and that the data was consistent in each table. All appears to be correct. I then tried:
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S
LEFT JOIN tblClassRoster as CR
ON S.Student_ID = CR.Student_ID
WHERE CR.Student_ID is null";
When I do this, I get all the students who are not in any class, so it runs correctly. Am I checking the values incorrectly? I've tried doing WHERE CR.Class_ID = \"$Class_ID_E\" and have gotten nothing. Are the single quotes correct? Am I just missing something completely?
The URL is:
http://...ClassRosterAdd.php3?Class_ID_E=NAA011&Class_Section_No_E=6&Class_Semester_E=%20SPRING&Class_Year_E=2001
Everything seems right to me. I know for some reason that the semester has something wrong with it (it adds spaces before 'SPRING') but that is consistent in both tables so that isn't the problem.
Thanks guys for any ideas and help you have. I've been working on this query for quite some time. I've done extensive SQL queries in Access but apparently I'm rusty. Thanks again!
-Neal
