I need PHP/SQL GURUS!!!!

[ZF_NeAlvey]

Hey guys. I've posted this a couple of times the past week or two around the internet 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

[bryce777]

You need an outer join, or the people you want will not be int he query at all to be checked in the first place.

Your join is eliminating those people before you ever get to your where clauses.

You might consider just getting everyone and programmatically removing the people in the class, since presumably it is smaller anyhow.

[Grizzly]

That query is fine syntactically speaking, but it has a loooooot of issues logically speaking. Without getting into too much detail, I'll just tell you how I would attack it. I would probably just run 2 queries:

Query 1:

SELECT Student_ID FROM tblClassRoster WHERE Class_ID = '$Class_ID_E'

Iterate through the returned recordset and build a comma delimited list of Student_ID's. Lets call it...$StudentsAlreadyInClassList

Query 2:

SELECT * FROM tblStudent WHERE Student_ID NOT IN($StudentsAlreadyInClassList)

Just an idea...that's what I would probably do. If I knew more about the data model I might have a cleaner solution for you, but without making too many assumptions, I'm pretty sure the above solution would work fine.

[ZF_NeAlvey]

I've tried the 2 queries once before and had no luck. I just tried them again and now I have a few problems.

I had what you posted, and it told me:
Unknown column 'Array' in 'where clause'


So then, I put $StudentsInClass.Student_ID and now I get:
Unknown table 'Array' in where clause

The $StudentsInClass is the query to select all of the students in the current selected class (Just as you had it, under a different name).

Can I create a new table using the values of $StudentsInClass or do you have any other ideas? I've been working on this query for entirely toooooo long. Any help would be GREAT!!!

-Neal

[Grizzly]

It sounds like you created the $StudentsAlreadyInClassList as an array, rather than a comma-delimited list. Check and see what you have for a value in the $StudentsAlreadyInClassList variable, it should look something like:

16,123,432,57,21,76,1,23,5,7,90

Or something to that effect. If it's an array, it won't work at all. The IN function is looking for a comma-delimited list, or a subquery.

Good luck!

[ZF_NeAlvey]

Grizzly, thank you very much for all the replies. I did look and I did have it as an array. I didn't even notice it. I did it by routine I guess. Anyway, here is problem...hopefully the last.

I have this:

$sql1 = "SELECT CR.Student_ID FROM tblClassRoster as CR
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'";

$StudentsInClass = mysql_query($sql1) or die(mysql_error());

$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S WHERE S.Student_ID NOT IN($StudentsInClass)";

When I:

echo "$StudentsInClass<br>";

I get:

Resource id #2


Sorry for all the questions. You are much more help than everyone else is atleast. Thanks again!

[Grizzly]

Ah I see your problem. I take it you haven't worked with PHP/MySQL too much before? the mysql_query() function does not return an array, it actually returns a "pointer" to a resulting recordset. You need to use the mysql_fetch_array() function to actually get a usable array out of the recordset. I've modified your code a little, which you should feel free to test out and see how it flies. I basically use a combination of the mysql_fetch_array() function, and the implode() function to transform the resulting recordset into a comma-delimited list.

$sql1 = "    SELECT CR.Student_ID 
        FROM tblClassRoster as CR 
        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'"; 

$StudentsInClass = mysql_query($sql1) or die(mysql_error());
$StudentsInClassList=implode(",",mysql_fetch_array($StudentsInClass));
echo $StudentsInClassList."<br>"; 

$sql = "    SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN 
        FROM tblStudent as S 
        WHERE S.Student_ID NOT IN($StudentsInClassList)"; 

[ZF_NeAlvey]

Grizzly,

Thank you very very very much. What you gave me was just enough for me to tinker with it and figure it out. Here is what I ended up with:

$sql1 = "SELECT CR.Student_ID FROM tblClassRoster as CR
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'";

if($StudentsInClass = mysql_query($sql1))
{
$StudentClass2=array();
while ($StudClass = mysql_fetch_array($StudentsInClass))
{
$StudentClass2[]=$StudClass['Student_ID'];
}
$StudentsInClassList = implode("," , $StudentClass2);
}
else
{
echo "SQL Error: ".mysql_error();
}

$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S
WHERE Student_ID NOT IN($StudentsInClassList)
ORDER BY S.Student_LN";

$Roster_Query = mysql_query($sql)
or die(mysql_error());

$Roster = mysql_fetch_array($Roster_Query);

while ($Roster = mysql_fetch_array($Roster_Query))
{
$Student_ID=stripslashes($Roster["Student_ID"]);
$Student_LN=stripslashes($Roster["Student_LN"]);
$Student_FN=stripslashes($Roster["Student_FN"]);
echo "<OPTION VALUE=\"$Student_ID\">$Student_ID -
$Student_LN, $Student_FN";
} // end while loop
?>

The code you gave me only put in the first row of the table into the array. I needed to continue to do that until the array was empty. I just need to go back and make sense of the variables and I'm done. Thanks again!

[ZF_NeAlvey]

One more thing, I also had to put this in towards the end of the above code.

if(count($StudentClass2)<1)
{
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN
FROM tblStudent as S
ORDER BY S.Student_LN";
}
else
{
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN
FROM tblStudent as S
WHERE Student_ID NOT IN($StudentsInClassList) ORDER BY S.Student_LN";
}

If I didn't add it, it would die if there was no one in the class. Now all once again seems to be great. Thanks yet one more time