foreach循环中的mysql更新

Question

I am trying to figure out how to update mysql table with array.

The table has 3 fields: id, rate, pol_id. The "insert into" works perfect:

 foreach ($rates as $rn=>$rv) {
 $sql3=mysql_query("INSERT into `rates` (`rate`, `pol_id`) 
             values ( '$rv', '$polid',)") or die ("Unable to issue query sql2:   ".mysql_error()); }

$rates is actually array from dynamic input fields.

So I get something like this example:

id  |   rate |    pol_id
=========================
1   |   5.6  |     272  
2   |   6.3  |     272 
3   |   7.9  |     272

Now I would like to edit the values in the input fields so I need to update the table:

I have tried this:

foreach ($rates as $rn=>$rv) {
$sql3=mysql_query("UPDATE `rates` SET `rate`='$rv' WHERE   `pol_id`='$polid'")or die ("Unable to issue query sql3: ".mysql_error()); }

But this isn't working, it updates all the rows with the last value.

Can ypu please help me with this?

Answer 1

Your $polid variable is not being changed inside the foreach loop. This causes your where statement to be true for all elements in the rates table that have the same pol_id (specified in $polid). This sets the value for 'all' entries (with pol_id = $polid) to the last value that you just entered (in $rv).

You could try adding a second condition to your where statement if you know the old value of rate to look like this:

$sql3=mysql_query("UPDATE `rates` SET `rate`='$rv' WHERE   `pol_id`='$polid' AND `rate`={old_value}")or die ("Unable to issue query sql3: ".mysql_error()); }

A nicer method is to use the id column of rates (as that is guaranteed to be unique) by first performing a query to retrieve the ID of the entry that you want and subsequently use that ID in the update query.

$query1 = mysql_query("select id from rates where {your desired condition};
/* Assign the result of $query1 to $id */
$sql3=mysql_query("UPDATE `rates` SET `rate`='$rv' WHERE   `pol_id`='$polid' AND `id` = $id)or die ("Unable to issue query sql3: ".mysql_error()); }

You can even let the pol_id = '$polid' out of the query now because of the id being unique.