INSERT INTO errors.

Client Joined: Oct 07, 2003 Posts: 735 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("<p>Error performing query: " . mysql_error() . "</p>");


      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.

Regular Joined: Jan 10, 2006 Posts: 93

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

Posted: Wed Mar 19, 2008 8:06 am

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.

Posted: Wed Mar 19, 2008 8:27 am

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.

Posted: Wed Mar 19, 2008 11:15 am

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.

Posted: Thu Mar 20, 2008 12:02 pm

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


      exit();   


      }

Posted: Mon Mar 24, 2008 7:37 am

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


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


      exit();   


      }


      


}

Posted: Mon Mar 24, 2008 7:43 am

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