增量更新DB字段

Question

我需要通过添加或减去一个数值来更新当前的DB字段值。我审查了“建议的答案”，但没有找到解决问题的办法。我的DB中的字段是标题"credits“，它的默认值是”0“。指定的DB引擎是InnoDB。我使用过程式php编码，下面是我目前的代码：

定义变量

    $id=$_SESSION['user_id'];  //id number for user
    $amount=$_POST['Amount']; //value of charges 12 character string
    $amount=ltrim($amount, '0'); //eliminates leading 0's
    $credit=$amount/1000; //converts value to credits
    $credit=number_format($credit, 2);  //formats credits with two decimal points

然后建立一个DB连接并执行以下查询

    $q="select credits from registration WHERE user_id=$id";
    $r=mysqli_query ($dbc, $q);
    $update="UPDATE registration SET credits = credits + $credit WHERE user_id=$id";

如果我“回显”变量，我看到所有的值，但“更新”失败。使用过程php编码，需要修改什么才能逐步更新“学分”字段？

Answer 1

//**i hope this helps**

<?php
$id=$_SESSION['user_id'];  //id number for user
$amount=$_POST['Amount']; //value of charges 12 character string
$amount=ltrim($amount, '0'); //eliminates leading 0's
$credit=$amount/1000; //converts value to credits
$credit=number_format($credit, 2);  //formats credits with two decimal points

$credits; // the credits from query select will be stored here

/* edit with your database parameters */
       $mysqli = new mysqli($host, $user, $password, $db);

       if(mysqli_connect_errno()) {
          echo "Connection Failed: " . mysqli_connect_errno();
          exit();
       }

       mysqli_set_charset($mysqli,"utf8");

if($stmt = $mysqli -> prepare("select credits from registration WHERE user_id=?"))
{
 $stmt -> bind_param("i", $id); //'i' means that ? is an integer value, and $id is the parameter for this value
 $stmt -> execute();
 $stmt -> bind_result($credits);
 $stmt -> fetch();
 $stmt -> close();

 $total=$credits+$credit;
 if($stmt = $mysqli->prepare("UPDATE registration SET credits = ? WHERE user_id=?"))
 {
  $stmt->bind_param("di",$total,$id);
  $stmt->execute();
  echo 'oki doki';
  $stmt->close();
 }

}

?>

Answer 2

对于闭包，我想提供解决我的问题的编码，它可能会帮助任何查看这个帖子的人。我确实相信但丁的解决方案使用“准备好的陈述”是最好的，但以下是我如何解决我的问题。

代码行添加：

    $row = mysqli_fetch_array($r, MYSQLI_ASSOC);
    $cr= sprintf ("%s", $row["credits"]);  //assign results to a variable

    $ttl=$cr+$credit;

将更新查询修改为：

    $update="UPDATE registration SET credits=$ttl WHERE user_id=$id";  
    $r = mysqli_query ($dbc, $update) or trigger_error("Query: $update\n<br />MySQL Error: " . mysqli_error($dbc));

这很好，我需要在给用户的消息中包含$ttl和$credit变量。我希望这能帮到你。