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

    Unanswered: Updating records

    I'm trying to update these records to cleanse them from a scantron I imported to a table.

    Here is a sample of the data.
    1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100
    1,000553939 ,"ANKROM, SEAN T",8,80,14,8,80
    1,000034194 ,"BALCIK, BRENDE ",9,90,35,9,90
    1,000065368 ,"BARNES, NATHAN ",10,100,75,10,100
    1,000345478 ,"BARTON, LUCAS ",10,100,75,10,100
    1,000114715 ,"BAXTER, COREY ",9,90,35,9,90
    1,xxxx75481 ,"LEE, LANCE ",9,90,35,9,90
    1,000546179 ,"LEVY, JESS D",8,80,14,8,80
    I needed to remove any leading x from the StudentID which I did with this.
    Code:
    $replacexuid = $db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = REPLACE(StudentID,'x','0')");
    Then I need to strip off the first character in the 9 character field and replace with a U. (For example U00388419)

    Code:
    $i = "U";
    $alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
    while ($row = $db->sql_fetchrow($alteruid)) { 
    $clean_UID = substr($row['StudentID'], -8);
    	   $new_UID = $i.$clean_UID;
    $db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = '$new_UID'");
      }
    The code is currently updating the very last record out of 104 from the query and updating all student grades with that same UID. This is incorrect as each student has their own StudentID.

    I am missing something that seems pretty easy.

    I dumped what the query was interpreting by doing this.

    Code:
    while ($row = $db->sql_fetchrow($alteruid)) {
       $clean_UID = substr($row['StudentID'], -8);
       $new_UID = $i.$clean_UID;
       $sql = "UPDATE ".$prefix."$table_name SET StudentID = '$new_UID'";
       die('<br />$sql = '.$sql.'<br />');
       $db->sql_query($sql);
    }
    The result was this

    Code:
    $sql = UPDATE atlas_tl_session_grade_import SET StudentID = 'U00077193'
    Which is the first record in the imported table.

    But at the end of the while loop it actually takes the last record read and then updates all records with 'U00000110'

    I think I need a foreach() but I don't know how to implement it.

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Your UPDATE query needs a WHERE clause to tell it which row to update. What the code apparently is doing now is updating every row to the same value every time it loops using the while() loop. You end up with the last value in everything.

  3. #3
    Join Date
    Nov 2002
    Posts
    32
    But I need them all to be updated.

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    An UPDATE with no WHERE clause updates all the rows to the same value. If that is what you want, leave your code as it is.

    If you want to update each individual row with a different new value, you need a WHERE clause to control which row gets updated with each specific value.

Posting Permissions

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