Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    32

    Unanswered: INSERT INTO errors.

    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?

  2. #2
    Join Date
    Nov 2002
    Posts
    32
    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....?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Posts
    32
    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.

  5. #5
    Join Date
    Nov 2002
    Posts
    32
    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),

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •