php - How to substract value from sum of values in Database

Question

I should substract given value from sum of values in Database!

For example: User "John" filled his account 2 time 100$ and 50$, Total in his account 150$ When he buy smth for 50$ We should substract from total money in his account! and total money should be updated.

Here code to display total money in his account:

 <?php
    session_start();
    $sql = "SELECT sum(payment_amount) as 'payment' from payments 
    WHERE username= '" . $_SESSION['username'] . "' ";
    $result = $db->query($sql);
    $row = $result->fetch_array(MYSQLI_ASSOC);
    echo "" . $row['payment'] . "";
    ?>

table: Payments

  ID      username    payment_amount    Status  
+-------+-------------+-------------+-----------+
|   1   |  John       |     100     | Complete  |
+-------+-------------+-------------+-----------+
|   2   |  John       |     50      | Complete  |
+-------+-------------+-------------+-----------+
|   3   |  Alex       |     100     | Complete  |
+-------+-------------+-------------+-----------+

标签： phphtmlmysql

Answer 1

当您使用 balance 时，您不应该这样做。

最好的方法是像这样创建一个新表。

身份证 | 用户名 | 平衡

然后每次付款，您只需在用户余额中使用 +$amount 更新数据库，而且，如果他花钱，您只需在更新中执行 -$amount。

例子：

tbl_user

身份证 | 姓名 | 电子邮件

1..亚历克斯[e-mail]

---

tbl_balance：

身份证 | 用户名 | 平衡

1....1........100