INSERT INTO errors.

sdonovan · #1 (**permalink**) 03-18-08, 14:57

I have a query where I'm trying to insert course grades.

A course can have several "sessions" where students participate in "Team Learning". During the all day course they take 3 exams. The whole course may be 3-4 weeks long where they may have 3 or 4 or more "Team Learning" sessions (or TL sessions for short).

I have a courses table with Course_ID.
I have a session table with Session_ID and Course_ID
I have a session_grades table with Session_ID and a SOMS_KEY (SOMS_KEY for each student), and a TL_Avg for each grade for that session.

My query looks like this:

Code:

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

For this example my course had 3 TL sessions. I need to get the AVG (TL_Avg) for all my sessions per course and insert it into the course_grades table.

This is happening but it is happening 3 times, where I just need it to be inserted once. If I need to limit the number of inserts would a simple left join work? if so where should I put that?

sdonovan · #2 (**permalink**) 03-24-08, 09:45

Somebody said I could not combine these two query's so I separated them.

This:

Code:

$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:

Code:

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....?

r937 · #3 (**permalink**) 03-24-08, 09:55

Quote:

Originally Posted by sdonovan

What the....?

that is exactly what i was thinking

it looks like a php question, not a mysql question

if you could remove all the php, maybe i'll take another look

sdonovan · #4 (**permalink**) 03-24-08, 11:43

This returns 104 record.

Code:

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

I need to insert to ".$prefix."_tl_course_grades

Code:

INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". "VALUES ('$Course_ID', '$SOMS_KEY', '$UID', '$Academic_Year', '$FinalGrade'

Each SOMS_KEY (student) in session_grades has more than one record. These are grades for all the exams they have taken. Each Session_ID has 104 records (grades) for all the students. A Course_ID can have several Session_ID's.

In the example I am testing each student has three exams (Session_ID's) for a single course.

sdonovan · #5 (**permalink**) 03-24-08, 11:49

This is what my session_grades looks like.

Code:

`atlas_tl_session_grades` (`Session_ID`, `SOMS_KEY`, `UID`, `Group_ID`, `IRAT_Raw`, `GRAT_Raw`, `AppEx_Raw`, `IRAT_Grade`, `GRAT_Grade`, `AppEx_Grade`, `TL_Avg`, `Academic_Year`, `Excused`) 
(46, 202, 'U00376103', 3, '6.0', '10.0', '5.0', '60.00', '100.00', '62.50', '74.88', '0709', 0),
(46, 203, 'U00376118', 28, '9.0', '10.0', '8.0', '90.00', '100.00', '100.00', '97.00', '0709', 0),
(46, 204, 'U00085285', 26, '8.0', '10.0', '7.0', '80.00', '100.00', '87.50', '89.63', '0709', 0),

(47, 202, 'U00376103', 3, '8.0', '9.0', '6.0', '80.00', '90.00', '75.00', '81.75', '0709', 0),
(47, 203, 'U00376118', 28, '9.0', '9.0', '6.0', '90.00', '90.00', '75.00', '84.75', '0709', 0),
(47, 204, 'U00085285', 26, '8.0', '10.0', '7.0', '80.00', '100.00', '87.50', '89.63', '0709', 0),

(48, 202, 'U00376103', 3, '5.0', '10.0', '9.0', '50.00', '100.00', '90.00', '81.50', '0709', 0),
(48, 203, 'U00376118', 28, '10.0', '10.0', '9.0', '100.00', '100.00', '90.00', '96.50', '0709', 0),
(48, 204, 'U00085285', 26, '9.0', '10.0', '10.0', '90.00', '100.00', '100.00', '97.00', '0709', 0),

Internet Services

Web Development

Digital Marketing

Consumer Tech