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

    Unanswered: INSERT INTO errors.

    I am trying to import data from a scantron. The import goes fine, I cleanse the data afterwords and then if there are no errors I INSERT INTO, but this so far is not working.

    I have looked at this for 3 days straight and can't find my error.

    I pass the Session_ID in from the url, and have tested that it is available. I have also echoed out the $Academic_Year.

    I am not getting any results from this
    Code:
    if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
    ...so it is hard to find out why this wont work. Any help is appreciated.

    Code:
    $Session_ID = $_GET['Session_ID'];
    //Get the academic year
    $Get_Academic_Year = $db->sql_query("SELECT Academic_Year FROM ".$prefix."_tl_config");  
    while ($info = $db->sql_fetchrow($Get_Academic_Year)) {
    		$Academic_Year = $info['Academic_Year']; 
    		}
    		
    //Check the Students UID and compare to the imported table.  They all need to match to continue.
    $checkUID = $db->sql_query("SELECT a.StudentID, b.U_Account  FROM ".$prefix."_tl_session_grade_import a
    JOIN ".$prefix."_tl_students b 
    WHERE a.StudentID != b.U_Account
    AND a.Session_ID = '$Session_ID'");
    
    $error_total = $db->sql_numrows($checkUID);
    //We have unmatched UID's!
    if ($error_total > 0) {
    OpenTable();
    echo"<tr><td>There are errors in the IRAT imported table.  These need to be fixed before writing to the grades table.  Ensure all student UID's match for all current students already in the system.</td></tr>";
    echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";
    Closetable();
    } else {
    //No errors detected so import IRAT grades from _tl_session_grade_import into _tl_session_grades table.
    $Grade_Type = 'IRAT';
    $getallinfo = $db->sql_query("SELECT b.SOMS_KEY, b.U_Account, a.Total_Percent, a.Session_ID FROM ".$prefix."_tl_session_grade_import a
    JOIN ".$prefix."_tl_students b
    ON (a.StudentID = b.U_Account)
    AND a.Session_ID = '$Session_ID'");
    if (!$getallinfo) {echo("<p>Error performing query: " . mysql_error() . "</p>");}  
    while ($row = $db->sql_fetchrow($getallinfo)) {
    		$SOMS_KEY = $row['SOMS_KEY']; 
    		$Session_ID = $row['Session_ID'];		
    		$UID = $row['U_Account']; 		
    		$Grade = $row['Total_Percent'];			
    
    $sql = $db->sql_query("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Grade, Grade_Type, Academic_Year)". "VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Grade', '$Grade_Type', '$Academic_Year')");
    if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}  
    }
    
    //Get total count of imported data
    $import_total = $db->sql_numrows($sql);
    Opentable();
    echo"<tr><td>$import_total Grades Added</td></tr>";
    CloseTable();  
    }

  2. #2
    Join Date
    Dec 2007
    Posts
    5
    Try changing:
    $sql = $db->sql_query("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Grade, Grade_Type, Academic_Year)". "VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Grade', '$Grade_Type', '$Academic_Year')");
    to:

    $sql = $db->sql_query(INSERT INTO .$prefix._tl_session_grades (Session_ID, SOMS_KEY, UID, Grade, Grade_Type, Academic_Year) VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Grade', '$Grade_Type', '$Academic_Year'));
    or:

    $sql = $db->sql_query(INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Grade, Grade_Type, Academic_Year) VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Grade', '$Grade_Type', '$Academic_Year'));

  3. #3
    Join Date
    Nov 2002
    Posts
    32
    I fixed this.

    here is how it works now.

    //Get the academic year
    $Get_Academic_Year = $db->sql_query("SELECT Academic_Year FROM ".$prefix."_tl_config");
    while ($info = $db->sql_fetchrow($Get_Academic_Year)) {
    $Academic_Year = $info['Academic_Year'];
    }

    //Check valid Students UID's and compare to the imported table
    $checkUID = $db->sql_query("SELECT a.StudentID FROM ".$prefix."_tl_session_grade_import a
    LEFT JOIN ".$prefix."_tl_students b ON a.StudentID = b.U_Account
    WHERE b.U_Account IS NULL");
    //AND a.Session_ID = '$Session_ID'");
    //WHERE a.Session_ID = '$Session_ID'");

    $error_total = $db->sql_numrows($checkUID);
    //We have unmatched UID's!
    if ( $error_total > 0 ) {
    OpenTable();
    echo"<tr><td>There are errors in the IRAT imported table. These need to be fixed before writing to the grades table. Ensure all student UID's match for all current students already in the system.</td></tr>";
    echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";
    Closetable();
    } else {
    //No errors detected so import IRAT grades from _tl_session_grade_import into _tl_session_grades table.

    $sql = ("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade)
    (SELECT a.Session_ID, b.SOMS_KEY, a.StudentID, c.Group_ID, a.Total_Percent FROM ".$prefix."_tl_session_grade_import a
    JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)
    JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)
    JOIN ".$prefix."_tl_session d
    WHERE a.Session_ID = d.Session_ID)");
    $result = $db->sql_query($sql);
    if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}

    //Get the academic year and update table
    $Get_Academic_Year = $db->sql_query("UPDATE ".$prefix."_tl_session_grades SET Academic_Year = (SELECT Academic_Year FROM ".$prefix."_tl_config)");

    //Drop Import table
    $sql = "DROP TABLE IF EXISTS ".$prefix."_tl_session_grade_import";
    $result = $db->sql_query($sql);
    if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
    echo $result;
    One thing I can't get working is the last part.

    DROP TABLE does not execute.

  4. #4
    Join Date
    Dec 2007
    Posts
    5
    When I execute a query I always use something like:

    $result = mysql_query($sql);

    Btw what are the contents of $prefix?

  5. #5
    Join Date
    Nov 2002
    Posts
    32
    I think this $result = $db->sql_query($sql); does the same thing.

    $prefix is a global variable that designated the prefix for all the table names. In this case it is atlas.

    So for example

    ".$prefix."_tl_session_grades

    is actually

    atlas_tl_session_grades

  6. #6
    Join Date
    Dec 2007
    Posts
    5
    Try looking into methods of concatenating within mysql code as it may be what is causing the problem.

Posting Permissions

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