dreamer1231
2017-11-29 13:43
浏览 8
已采纳

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...

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtfo55908 2017-11-29 13:51
    已采纳

    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']");
    }
    
    已采纳该答案
    打赏 评论
  • dsf45346 2017-11-29 13:55

    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.

    打赏 评论

相关推荐 更多相似问题