[SOLVED] Updating records

[Donovan]

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.

$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)

 

$i = "U";
$alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
while ($row = $db->sql_fetchrow($alteruid)) { 
$clean_UID = substr($row['StudentID'], -;
   $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.

 

while ($row = $db->sql_fetchrow($alteruid)) {
   $clean_UID = substr($row['StudentID'], -;
   $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

 

$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.

 

I need all records updated regardless so I don't think I need a WHERE clause.

[adam291086]

edit

 

try setting up a varible that == the table name with $prefix added

[corillo181]

how you try using num_rows to see how many records are been return?

[Donovan]

The table name is defined in the beginning of the code.

 

$table_name = "_tl_session_grade_import";

 

The $prefix is global and it is defined as atlas.

 

The following displays how many records are imported.

 

$import_total = $db->sql_numrows($db->sql_query("SELECT * FROM ".$prefix."$table_name"));
Opentable();
echo" $import_total records were imported \n";

 

I am importing 104 records from a scantron file which is a csv file.

 

 

[PFMaBiSmAd]

Your UPDATE query needs a WHERE clause to tell it which row to update. Otherwise it updates all the rows with the same value, like it is doing now.

[Donovan]

I need all rows updated... regardless.  The field I am updating is the only field where I could place a WHERE clause but it doesn't match any other value I have in other tables.

 

The UID in my students table all are 9 characters starting with a U

 

The StudentID in the imported table are 8 character and need a U appended to the beginning.

 

Could I somehow change this and instead load an array with all StudentID from the imported table and just loop thru it and add a U.

 

$numgrades = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
$grade_total = $db->sql_numrows($numgrades);
for ($i=0; $i <= $grade_total; $i++) {
Code for adding U
}

 

 

 

 

 

[Donovan]

I tried the following:

 

$i = "U";
$alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
while ($row = $db->sql_fetchrow($alteruid)) { 
$clean_UID = substr($row['StudentID'], -;
   $new_UID = $i.$clean_UID;
$db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = '$new_UID' WHERE StudentID != 0");
  }

 

And it updated all rows with the StudentID of the "first" record read.

[PFMaBiSmAd]

Replace all that php with a query that does this -

 

UPDATE $prefix$table_name SET StudentID = INSERT(StudentID, 1, 1, 'U')

[Donovan]

Replace all that php with a query that does this -

 

UPDATE $prefix$table_name SET StudentID = INSERT(StudentID, 1, 1, 'U')

 

 

I think you are a life saver.

 

This

 

$replacexuid = $db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = REPLACE(StudentID,'x','0')");

 

along with your code

 

$db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = INSERT(StudentID, 1, 1, 'U')");

 

I think has done the trick.

 

Can you explain this method?  It is not INSERT INTO, nor is it INSERT VALUES.

 

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html