php / mysql update for multiple values in only one field?

Question

I have a table (say table name TBL fields FLD1, FLD2, FLD3) with existing data in it. I've recently added another field to it - FLD4. What I'm trying to do is to insert values into that new field only... for multiple records (about 1000). I'm trying to accomplish this with two arrays, but it's not working.
Here is what I'm doing...


in php:
arrrayOfValues = array();
arrayOfValuesToUpdate = array();
// Both arrays have values in them... they're both equal in length...

for($i=0; $i$result = myslq_query(UPDATE TBL SET FLD4='arrayOfValuesToUpdate[$i]' WHERE FLD1='arrayOfValues[$i]');
}
I'm not sure if that should even work for that many records...
Any ideas? :)
Thanks!

Answer 1

$arrrayOfValues=array(1,2,3);

$arrrayOfupdates=array("a","b","c");

for($i=0; $i< count($arrrayOfValues);$i++)

{

if(!empty($arrrayOfupdates[$i]))

$sql[]=sprintf("UPDATE table SET field4='%s' WHERE Idfield='%d'" ,

$arrrayOfupdates[$i]

, $arrrayOfValues[$i] );

}

foreach ($sql AS $lines)

mysql_query($lines) or die("error in query" . mysql_error() . " - " . $lines);



?>

Answer 2

The logic looks right.



But I think you need quotes around the UPDATE statement, because it's a string.



Try doing it in smaller steps, and print out the SQL UPDATE statements as the program runs, this way it will be easier to debug:



for($i=0; $i
$SQL="UPDATE TBL SET FLD4='arrayOfValuesToUpdate[$i]' WHERE FLD1='arrayOfValues[$i]'";

print $SQL;

$result = myslq_query($SQL);

}

Answer 3

What's your primary key? The best way to update the fld4 would be something like this...



set $fld1, $fld2 and $fld3 to be equal to the first three fields, and $fld4 to be what you want it to become...



then:



mysql_query("UPDATE tbl SET fld4 = '".mysql_real_escape_string($fld4)."' WHERE fld1 = '$fld1' AND fld2 = '$fld2' and fld3 = '$fld3' LIMIT 1');



If, for instance, Fld1 is yor primary key, you can ignore the rest of the line after " = '$fld1' as that will be sufficient to identify the row to MySQL.