INSERT INTO errors.

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

Another issue.

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 tl_courses table with Course_ID.
I have a tl_session table with Session_ID and Course_ID
I have a tl_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("Error performing query: " . mysql_error() . "");
 exit();
 }

For this example my course had 3 TL sessions. The AVG(TL_Avg) seems to work and computes the average for all 3 TL sessions per course and inserts it into the course_grades table.

The problem is it is inserting the record 3 times so now I have 3 duplicate records, where I just need it to be inserted once.

Nash · Regular Joined: Jan 10, 2006 Posts: 86

do any of these tables have multiple entries for a particular session_ID or course_ID?

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

The session_grade table has 3 records all with the same SOMS_KEYS for a student but with different Session_ID's. Those 3 Session_ID's belong to a single course.

Nash · Regular Joined: Jan 10, 2006 Posts: 86

hmm, would have to play around with it. I did a bunch of basic Access programming at the end of last summer and remember having issues with duplicates occurring in my queries when I would join tables in single-to-many relationships, but I resolved them all after I figured out the right types of joins to use.

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

Let me explain:

Here is what my session_grades looks like

Quote:

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 73.00 0709 0
47 202 U00376103 3 8.0 9.0 6.0 80.00 90.00 75.00 81.00 0709 0
48 202 U00376103 3 5.0 10.0 9.0 50.00 100.00 90.00 81.00 0709 0

I have three different Session_ID's all for the same student (SOMS_KEY). Each session grade has been averaged and written to TL_Avg based on a weighted value. (IRAT * .03) + GRAT *.03) + (Appex * .04) or something similar. I now want to get the average of all three TL_Avg and write that to the course_grades table. This is happening but I am writing the same record three different times.

I have to join the table back like so:

Code:

INSERT INTO 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 _session_grades g
JOIN session s ON (g.Session_ID = s.Session_ID)
JOIN courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID'

to make sure I am processing the correct grades for the Course I have chosen.

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

How can this query:

Code:

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 inserts 312 records?

Code:

$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("Error performing query: " . mysql_error() . "");
 exit();
 }

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

Even if I rewrite this I still get the same 312 records inserted. Even though I run this SELECT query in phpMyadmin I only get 104 which is what I want.

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("Error performing query: " . mysql_error() . "");
 exit();
 }
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("Error performing query: " . mysql_error() . "");
 exit();
 }

}

Donovan · Client Joined: Oct 07, 2003 Posts: 661 Location: Ohio

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("Error performing query: " . mysql_error() . "");
 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("Error performing query: " . mysql_error() . "");
 exit();
 }

}

What the....?