PHP和MYSQL计算学生总余额

Question

我有这个问题，首先要获取学生总数，然后是 amount_paid 列中学生支付的总金额，我正在查询所有学生 id(system_id)，然后是每笔付款，然后是发票和发票的成本费用目标是与学生匹配的 id。最后，我尝试减去循环中发送给选定学生的所有发票的总金额，然后从同一循环中发送给目标学生的费用中减去它。现在，我的许多问题是，对于第一批学生来说，它正在计算余额罚款，但其余的余额正在迅速增加。请问我的代码问题出在哪里：<?php$students = mysqli_query($conn, "SELECT * FROM students GROUP BY system_id");while($row = mysqli_fetch_assoc($students)){    $user_uid = $row['system_id'];    $exam = $row['exam_number'];   //getting payments balances//gettingshopping cart details      $Balance_query = mysqli_query($conn,  "SELECT SUM(amount_paid) AS 'sumitem_cost' FROM payments WHERE payment_by='$user_uid' ");          $balance_data = mysqli_fetch_array($Balance_query);      $balance_price = $balance_data['sumitem_cost'];            $py = mysqli_query($conn, "SELECT * FROM payments WHERE payment_by='$user_uid' AND status!='rejected' GROUP BY invoice_id");      while($rowpy = mysqli_fetch_assoc($py)){                    $paidAmout = $rowpy['amount'];          $invoiceId = mysqli_real_escape_string($conn, $rowpy['invoice_id']);          $PaymentStatus = mysqli_real_escape_string($conn, $rowpy['status']);          //Getting invoice          $Invoice = mysqli_query($conn, "SELECT * FROM invoices WHERE id='$invoiceId'");                    while($rowInv = mysqli_fetch_assoc($Invoice)){          $NewFeeId = $rowInv['id'];                    $sql = mysqli_query($conn,"SELECT SUM(fee) as total FROM invoices WHERE id='$invoiceId'");         $row = mysqli_fetch_array($sql);          $sum = $row['total'];          $total_price += $row[‘fee’];      }}     echo'<br>'.$exam.':' . $BalanceToPay =  $total_price - $balance_price;      }?>

Answer 1

你有一些奇怪的疑问。单引号绝对不能用于列别名，为什么要按 system_id 分组？通过使用联接，您的计算将大大简化，如果我是您的老师，我希望看到联接。

这将立即解决分配问题：

SELECT s.system_id

,      sum(amount_paid) as sumitem_cost

,      sum(fee) as total

FROM students s

JOIN payments p on s.system_id = p.payment_by

JOIN invoices i on p.invoice_id = i.id

Answer 2

$total_price每次与新学生打交道时，您都需要归零（init） 。您没有明确地这样做，因此第一次使用$total_price它时会被创建为零，但随后您会进一步迭代并不断添加它，最终得到累积值。所以只需添加

$total_price = 0;

对于每个while循环迭代：

while($row = mysqli_fetch_assoc($students)){

    $total_price = 0;

    ...

Answer 3

你可以试试这个代码

    <?php

$students = mysqli_query($conn, "SELECT * FROM students GROUP BY system_id");

$grandBalanceForAllStudent = 0 ;

while($row = mysqli_fetch_assoc($students)){

    $user_uid = $row['system_id'];

    $exam = $row['exam_number'];

    $total_price = 0 ;

//getting payments balances

//gettingshopping cart details

      $Balance_query = mysqli_query($conn,  "SELECT SUM(amount_paid) AS 'sumitem_cost' FROM payments WHERE payment_by='$user_uid' ");

    

      $balance_data = mysqli_fetch_array($Balance_query);

      $balance_price = $balance_data['sumitem_cost'];

      

      $py = mysqli_query($conn, "SELECT * FROM payments WHERE payment_by='$user_uid' AND status!='rejected' GROUP BY invoice_id");

      while($rowpy = mysqli_fetch_assoc($py)){

          

          $paidAmout = $rowpy['amount'];

          $invoiceId = mysqli_real_escape_string($conn, $rowpy['invoice_id']);

          $PaymentStatus = mysqli_real_escape_string($conn, $rowpy['status']);

          //Getting invoice

          $Invoice = mysqli_query($conn, "SELECT * FROM invoices WHERE id='$invoiceId'");

          

          while($rowInv = mysqli_fetch_assoc($Invoice)){

          $NewFeeId = $rowInv['id'];

          

          $sql = mysqli_query($conn,"SELECT SUM(fee) as total FROM invoices WHERE id='$invoiceId'");

         $row = mysqli_fetch_array($sql);

          $sum = $row['total'];

          $total_price += $row[‘fee’];

          

      }}

      $BalanceToPay =  $total_price - $balance_price;

      // you can keep for your data 

      $grandBalanceForAllStudent += $BalanceToPay ;

     echo'<br>'.$exam.':' . $BalanceToPay ;

      

}

?>