#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    12

    Writing values back from temp table.


    I created a temp grade table to compute some variables before I write them back to the grade table. The problem is it seems values from other Course_Numbers are getting NULL'ed in the grade table.

    Here is some of my code.
    PHP Code:
    //Get Multiplier value        
    $updateMultiplier $db->sql_query("UPDATE TempGradeTable tt
    SET Multiplier = (SELECT AVG(evalscore) * .1 AS Multiplier
    FROM "
    .$prefix."_tl_peereval pe
    WHERE tt.Course_Number = pe.Course_Number
    AND tt.UID = pe.UID)"
    );
    if (!
    $updateMultiplier) {
            echo(
    "<p>Error performing query: " mysql_error() . "</p>");
            exit();   
            }

    //Write mult value back to course table        
    $writeMultiplier $db->sql_query("UPDATE ".$prefix."_tl_course_grades cg
    SET Multiplier = (SELECT Multiplier FROM TempGradeTable tt
    WHERE tt.Course_Number = cg.Course_Number
    AND tt.UID = cg.UID)"
    );
    if (!
    $writeMultiplier) {
            echo(
    "<p>Error performing query: " mysql_error() . "</p>");
            exit();   
            }    

    //Get Group GRAT and Appex grade avg
    $muxGratGrade $db->sql_query("UPDATE TempGradeTable tt
    SET GRT_APX_AVG = (SELECT ((GRAT_Total + AppEx_Total) / 2) AS GRT_APX_AVG
    FROM "
    .$prefix."_tl_course_grades cg
    WHERE cg.Course_Number = tt.Course_Number
    AND tt.UID = cg.UID)"
    );
    if (!
    $muxGratGrade) {
            echo(
    "<p>Error performing query: " mysql_error() . "</p>");
            exit();   
            }
            
    //Write GRAT/Appex grade avg back to course table
    $updateGRT_APXGrade $db->sql_query("UPDATE ".$prefix."_tl_course_grades cg
    SET GRT_APX_AVG = (SELECT GRT_APX_AVG FROM TempGradeTable tt
    WHERE cg.Course_Number = tt.Course_Number
    AND tt.UID = cg.UID)"
    );
    if (!
    $updateGRT_APXGrade) {
            echo(
    "<p>Error performing query: " mysql_error() . "</p>");
            exit();   
            } 
    So in my table I process all grades for Course_Number = 571

    I write back the following variables

    Multiplier
    GRT_APX_AVG
    GRT_APX_MULT

    You see the code for Multiplier and GRT_APX_AVG and the other is just about identical.

    After I process grades for Course_Number = 533

    The values for 571

    Multiplier
    GRT_APX_AVG
    GRT_APX_MULT

    Are NULL.

    Why is that?

    I think this has to do with my parentheses and how I have groups my SELECT statement when I write back to the grade table.

    PHP Code:
    ("UPDATE ".$prefix."_tl_course_grades cg
    SET Multiplier = (SELECT Multiplier FROM TempGradeTable tt
    WHERE tt.Course_Number = cg.Course_Number
    AND tt.UID = cg.UID)"

  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    12
    Nevermind.

    Instead of doing subqueries I did inner joins.

    PHP Code:
    //Write mult value back to course table        
    $writeMultiplier $db->sql_query("UPDATE ".$prefix."_tl_course_grades cg
    INNER JOIN TempGradeTable tt ON (tt.UID = cg.UID)
    SET cg.Multiplier = tt.Multiplier
    WHERE tt.Course_Number = cg.Course_Number
    AND tt.UID = cg.UID"
    );
    if (!
    $writeMultiplier) {
            echo(
    "<p>Error performing query: " mysql_error() . "</p>");
            exit();   
            } 

    Comments on this post

    • Winters agrees

IMN logo majestic logo threadwatch logo seochat tools logo