Sub Query error

SFDonovan

How can this query:

SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG( TL_Avg ) AS Final_Grade
FROM atlas_tl_session_grades sg
JOIN atlas_tl_session s ON ( s.Session_ID = sg.Session_ID )
WHERE s.Course_ID = 'CL00000011'
GROUP BY sg.SOMS_KEY

gives me 104 records which is what I want.

Yet this next query insert 312 records?

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". "SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG(TL_Avg) AS FinalGrade
FROM ".$prefix."_tl_session_grades sg
JOIN ".$prefix."_tl_session s ON (s.Session_ID = sg.Session_ID)
WHERE s.Course_ID = '$Course_ID'
GROUP BY sg.SOMS_KEY"); 
if (!$insertgrades) {
		echo("<p>Error performing query: " . mysql_error() . "</p>");
		exit();   

		}

peytonrm

I don't see anything in the query that would cause that. My guess is the file containing the query is being included multiple times or a variable is not set to what you think it is or something wacky like that in your php. I would hard-code the variables and try to run that query directly on the mysql server. If that gives you the correct results, you know it's a problem in the php somewhere. If it that gives the wrong results, I'm stumped.

mwasif

Like peytonrm said 🙂
104 * 3 = 312

SFDonovan

Somebody said I could not combine the two query's together. The SELECT and INSERT INTO.

So..

I hardcoded it and I get back 104.

This:

$getgrades = $db->sql_query("SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG(TL_Avg) AS FinalGrade
FROM ".$prefix."_tl_session_grades sg
JOIN ".$prefix."_tl_session s ON (s.Session_ID = sg.Session_ID)
WHERE s.Course_ID = '$Course_ID'
GROUP BY sg.SOMS_KEY"); 
if (!$getgrades) {
		echo("<p>Error performing query: " . mysql_error() . "</p>");
		exit();   

		}
$total = $db->sql_numrows($getgrades);
OpenTable();
echo"<tr><td>$total</td></tr>";
CloseTable();
die();

Displays 104

While the rest of the code will insert 312 records:

while($row = $db->sql_fetchrow($getgrades)) {
$Course_ID = $row[Course_ID];
$SOMS_KEY = $row[SOMS_KEY];
$UID = $row[UID];
$Academic_Year = $row[Academic_Year];
$FinalGrade = $row[FinalGrade];

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". "VALUES ('$Course_ID', '$SOMS_KEY', '$UID', '$Academic_Year', '$FinalGrade')");
if (!$insertgrades) {
		echo("<p>Error performing query: " . mysql_error() . "</p>");
		exit();   

		}

}

What the....?