PHP MySQLi - 在一个进程中使用某个公式更新多个数据

问题描述：

How to update many rows in a mysqli table? Here my table (tbmath)

|id | code    |a   | b | c   |
| 1 |  1      |11 | 11 |     |
| 2 |  1      |12 | 22 |     |
| 3 |  1      |13 | 33 |     |
| 1 |  2      |11 | 11 |     |
| 2 |  2      |12 | 22 |     |
| 3 |  2      |13 | 33 |     |

... Code is for the operation rules Code 1 = a + b; Code 2 = a x b;

Im try'n to update c with code = 1 first, but i have the wrong result, hope somebody can help me to fix this code or give me the right why to solve the problem. Here my code :

<?php
 $db_host="localhost";
 $db_user="root";
 $db_pass="";
 $db_database="test";
 $con = new 
 mysqli($db_host,$db_user,$db_pass,$db_database);

 $sql=mysqli_query($con,"SELECT * FROM tbmath 
 WHERE code = 1");
 while ($a=mysqli_fetch_assoc($sql)) 
{
    $c=$a['a']+$a['b'];
    mysqli_query($con,"UPDATE `tbmath` SET `c` = '$c' 
    WHERE `code` = 1");
}
 ?>

The result is :

|id | code    | a | b  | c |
| 1 |  1      |11 | 11 |46 |
| 2 |  1      |12 | 22 |46 |
| 3 |  1      |13 | 33 |46 |
| 1 |  2      |11 | 11 |   |
| 2 |  2      |12 | 22 |   |
| 3 |  2      |13 | 33 |   |

My code just calculating the last result 13 + 33.

What's the right PHP code for my case ? I hope somebody can help me...

答

You must use id field param in your where criteria, not code field. Because you getting code equals 1 data set.

$db_host="localhost";
 $db_user="root";
 $db_pass="";
 $db_database="test";
 $con = new 
 mysqli($db_host,$db_user,$db_pass,$db_database);

 $sql=mysqli_query($con,"SELECT * FROM tbmath 
 WHERE code = 1");
 while ($a=mysqli_fetch_assoc($sql)) 
{
    $c=$a['a']+$a['b'];
    mysqli_query($con,"UPDATE `tbmath` SET `c` = '$c' 
    WHERE `id` = $a['id']");
}

答

You can do one update for each operation type, making the whole process way faster when many entries are in the db.

UPDATE tbmath 
SET c = a + b 
WHERE code = 1;

UPDATE tbmath 
SET c = a * b
WHERE code = 2;

I would also like to point out, that the id column does not seem to be unique. is that intentional? Usually ids are unique ;) This code should work even if they're not unique, but non-unique ids may cause other problems in your code/framework.

PHP MySQLi - 在一个进程中使用某个公式更新多个数据

相关推荐