Updating records

Donovan · Client Joined: Oct 07, 2003 Posts: 688 Location: Ohio

I'm trying to update these records to cleanse them from a scantron. 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 a 9 character field and replace with a U

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 UID. This is incorrect as each student has their own StudentID.

I missing something that seems pretty easy.

Raven · Posted: Thu Dec 13, 2007 9:22 am

I always dump what my interpreted query will look like to try to resolve the problem. Try modifying your code to read as follows to be sure it is forming your query correctly.

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);
}

That will at least let you know if that is or isn't the problem.

Donovan · Client Joined: Oct 07, 2003 Posts: 688 Location: Ohio

The result tells me

Code:

$sql = UPDATE atlas_tl_session_grade_import SET StudentID = 'U00077193'

Which is the first record in the imported table.

But after it finishes it actually takes the last record read and then updates all records with 'U00000110'

Donovan · Client Joined: Oct 07, 2003 Posts: 688 Location: Ohio

Maybe something like...

Code:

while ($row = $db->sql_fetchrow($alteruid)) {
$clean_UID = substr($row['StudentID'], -8);
$new_UID = $i.$clean_UID;
foreach ($row['StudentID'] as $new_UID) {
$sql = "UPDATE ".$prefix."$table_name SET StudentID = '$new_UID'";
$db->sql_query($sql);
}
}

But this tells me I have an error with

Code:

foreach ($row['StudentID'] as $new_UID) {

Quote:

Warning: Invalid argument supplied for foreach()

...repeated 104 times. I guess that is a good sign Laughing

Donovan · Client Joined: Oct 07, 2003 Posts: 688 Location: Ohio

Once I get these imported correctly I have the next thing to do which is to match them to my students table. Since the StudentID may be incorrect I will also need to match on the Student Name.

The Student_Name in the imported table would look something like this:

BERENSON, KATE F

And the field 'Last_Name' in my student table would be:

Berenson

How could I do this in a single query to try and match up each record?

Here is my first attempt... it is pretty rough:

Code:

$iratimported = $db->sql_query("SELECT a.*, strtoupper(b.Name_Last), b.U_Account FROM ".$prefix."_tl_session_grade_import a
JOIN ".$prefix."_tl_students b WHERE (b.Name_Last LIKE a.StudentName% || b.U_Account == a.StudentID)");

Another option is to split the Student_Name into Name_First and Name_Last when I import the csv. Then I could do without the LIKE operator

An example record of the csv looks like

Quote:

1,000544030 ,"BERENSON, KATE F",9,90,35,9,90