Update multiple records

[hperez]

Can someone please help me figure out how to update multiple values all at one time? I have a database that holds the following values: item, part_number, price. I want to be able to update the part_numbers and prices all at once but I cannot find anything anywhere that tells me how to accomplish this. At first I stumbled across this script that seemed promissing:

 

<?php

$dbServer = \"myserver

$dbUser = \"user\";

$dbPass = \"password\";

$dbName = \"mydb\";

 

function ConnectToDb($server, $user, $pass, $database)

{

$s = @mysql_connect($server, $user, $pass);

$d = @mysql_select_db($database, $s);

 

if(!$s || !$d)

return false;

else

return true;

}

$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);

 

$part_numbers=array(\"3930222\",\"493097\");

$prices=array(\"383.00\",\"493.89\");

 

$TheCount=count($prices);

 

for($a=0; $a < $TheCount; $a++)

{

$SQL= \"UPDATE xc90 SET price=\'$prices[$a]\' WHERE part_number=\'$part_numbers[$a]\'\";

}

if ( mysql_query($SQL) )

{

echo(\"<P>Prices successfully updated!</P>\");}

else

{

echo(\"<P>Error updating prices: \" . mysql_error() . \"</P>\");

}

?>

 

This works partially because it only updates the very last value to the right in both arrays and ignores all those before it.

 

I was also wondering if the is there a way to use the LOAD DATA IN FILE command to accomplish this task? I have a .txt file with the part_numbers and prices, but when I try to use the command LOAD DATA INFILE \"xc90.txt\" INTO TABLE xc90 (part_number,price) it appends the data as opposed to overwrite all records from the beggining with the new ones.

 

If someone could please help me I\'d be very appreciative. Thanks in advance for any help.