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

    Unanswered: Writing calculations back to table

    I am working on a grading app for the university I work for. So far this is what I have:

    After gathering the grade info I insert to a table

    Code:
    $insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Irat_Avg, Grat_Avg, Appex_Avg) SELECT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(IRAT_Grade) AS Irat_Avg, AVG(GRAT_Grade) AS Grat_Avg, AVG(Appex_Grade) AS Appex_Avg
    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();   
    		}

    I then compute another average of Appex and Grat and write that to GRAT_AppEx_Avg. I don't know if this is correct but it appears to be working.

    Code:
    $updategroupavg = $db->sql_query("UPDATE ".$prefix."_tl_course_grades SET GRAT_AppEx_Avg = (SELECT AVG(Grat_Avg + Appex_Avg)/2 AS GRAT_AppEx_Avg)");
    if (!$updategroupavg) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    I then select the "weights" to assign for each part of the grade. Some parts are weighted more than others. 20%, 40% etc.

    Code:
    $getweightinfo = $db->sql_query("SELECT * FROM ".$prefix."_tl_class_weights cw JOIN ".$prefix."_tl_weights w ON cw.weight_id = w.weight_id WHERE Class_Year = '$Course_Year'");
    if (!$getweightinfo) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    while($info = $db->sql_fetchrow($getweightinfo)) {
    $irat_wt = $info[irat_wt];
    $grat_wt = $info[grat_wt];
    $appex_wt = $info[appex_wt];
    }
    Then I am trying to compute the Final grade and here is where I need some help.

    Code:
    $processfinalgrade = $db->sql_query("SELECT * FROM ".$prefix."_tl_course_grades WHERE Course_ID = '$Course_ID'");
    if (!$processfinalgrade) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    while($row = $db->sql_fetchrow($processfinalgrade)) {
    $IRAT_Avg = $row[IRAT_Avg];
    $GRAT_Avg = $row[GRAT_Avg];
    $Appex_Avg = $row[Appex_Avg];
    $Grade = (($IRAT_Avg * $irat_wt) + ($GRAT_Avg * $grat_wt) + ($Appex_Avg * $appex_wt));
    }
    Update the table

    Code:
    $updatefinalgrade = $db->sql_query("UPDATE ".$prefix."_tl_course_grades SET Final_Grade = '$Grade' WHERE Course_ID ='$Course_ID'");
    ...which is not writing the correct Final grade. I get a repeating 67%.

    I think I am selecting the last value for $Grade and updating all records with that value.

    Any help is appreciated.

    Here is the entire code.
    Code:
    $Course_ID = $_GET['Course_ID'];
    $getcourseinfo = $db->sql_query("SELECT status, Session_Detail, Course_Year FROM ".$prefix."_tl_session s JOIN ".$prefix."_tl_courses c ON s.Course_ID = c.Course_ID WHERE s.Course_ID = '$Course_ID'");
    while(list($status, $Session_Detail, $Course_Year) = $db->sql_fetchrow($getcourseinfo)) {
    //while($row = $db->sql_fetchrow($getcourseinfo)) {
    if ($status == 0) {
    OpenTable();
    echo"<tr><td>TL session(s) for this course are not complete.</td></tr>";
    CloseTable();
    die();
    } else {
    $insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Irat_Avg, Grat_Avg, Appex_Avg) SELECT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(IRAT_Grade) AS Irat_Avg, AVG(GRAT_Grade) AS Grat_Avg, AVG(Appex_Grade) AS Appex_Avg
    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();   
    		}
    
    
    $updategroupavg = $db->sql_query("UPDATE ".$prefix."_tl_course_grades SET GRAT_AppEx_Avg = (SELECT AVG(Grat_Avg + Appex_Avg)/2 AS GRAT_AppEx_Avg)");
    if (!$updategroupavg) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    
    $getweightinfo = $db->sql_query("SELECT * FROM ".$prefix."_tl_class_weights cw JOIN ".$prefix."_tl_weights w ON cw.weight_id = w.weight_id WHERE Class_Year = '$Course_Year'");
    if (!$getweightinfo) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    while($info = $db->sql_fetchrow($getweightinfo)) {
    $irat_wt = $info[irat_wt];
    $grat_wt = $info[grat_wt];
    $appex_wt = $info[appex_wt];
    }
    
    $processfinalgrade = $db->sql_query("SELECT * FROM ".$prefix."_tl_course_grades WHERE Course_ID = '$Course_ID'");
    if (!$processfinalgrade) {
    		echo("<p>Error performing query: " . mysql_error() . "</p>");
    		exit();   
    		}
    while($row = $db->sql_fetchrow($processfinalgrade)) {
    $IRAT_Avg = $row[IRAT_Avg];
    $GRAT_Avg = $row[GRAT_Avg];
    $Appex_Avg = $row[Appex_Avg];
    $Grade = (($IRAT_Avg * $irat_wt) + ($GRAT_Avg * $grat_wt) + ($Appex_Avg * $appex_wt));
    //$updatefinalgrade = "UPDATE ".$prefix."_tl_course_grades SET Final_Grade = '$Grade' WHERE Course_ID ='$Course_ID'";
    //$result = $db->sql_query($updatefinalgrade);
    //if (!$result) {
    	//	echo("<p>Error performing query: " . mysql_error() . "</p>");
    		//exit();   
    		//}
    //die('<br />$processfinalgrade = '.$processfinalgrade.'<br />'); 
    OpenTable();
    echo"<tr><td>$IRAT_Avg </td>";
    echo"<td>$GRAT_Avg </td>";
    echo"<td>$Appex_Avg </td>";
    echo"<td>$Grade </td></tr>";
    CloseTable();
    }
    //$updatefinalgrade = $db->sql_query("UPDATE ".$prefix."_tl_course_grades SET Final_Grade = '$Grade' WHERE Course_ID ='$Course_ID'");
    //die('<br />$updatefinalgrade = '.$updatefinalgrade.'<br />'); 
    }
    header("Location: ".$admin_file.".php?op=TLViewCourseGrades&Course_ID='$Course_ID'");	
    }

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you tried debugging the sql to make sure that the values are correct.

    either do a die echo sql
    or write a series of liens to a file

    Id want to see what data was being processed
    see what the revised grade is
    see what the SQL statements are (ie am i getting the right data, am i updating the right rows with the right values)

    its not too difficult
    you could whilst debugging dump all that to a variable and add it to the URL you got to at the end of the script and display it in that script

Posting Permissions

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