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,&nbsp; &nbsp; &nbsp; sum(amount_paid) as sumitem_cost,&nbsp; &nbsp; &nbsp; sum(fee) as totalFROM students sJOIN payments p on s.system_id = p.payment_byJOIN 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)){&nbsp; &nbsp; $total_price = 0;&nbsp; &nbsp; ...

Answer 3

你可以试试这个代码&nbsp; &nbsp; <?php$students = mysqli_query($conn, "SELECT * FROM students GROUP BY system_id");$grandBalanceForAllStudent = 0 ;while($row = mysqli_fetch_assoc($students)){&nbsp; &nbsp; $user_uid = $row['system_id'];&nbsp; &nbsp; $exam = $row['exam_number'];&nbsp; &nbsp; $total_price = 0 ;//getting payments balances//gettingshopping cart details&nbsp; &nbsp; &nbsp; $Balance_query = mysqli_query($conn,&nbsp; "SELECT SUM(amount_paid) AS 'sumitem_cost' FROM payments WHERE payment_by='$user_uid' ");&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; $balance_data = mysqli_fetch_array($Balance_query);&nbsp; &nbsp; &nbsp; $balance_price = $balance_data['sumitem_cost'];&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; $py = mysqli_query($conn, "SELECT * FROM payments WHERE payment_by='$user_uid' AND status!='rejected' GROUP BY invoice_id");&nbsp; &nbsp; &nbsp; while($rowpy = mysqli_fetch_assoc($py)){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $paidAmout = $rowpy['amount'];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $invoiceId = mysqli_real_escape_string($conn, $rowpy['invoice_id']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $PaymentStatus = mysqli_real_escape_string($conn, $rowpy['status']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //Getting invoice&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $Invoice = mysqli_query($conn, "SELECT * FROM invoices WHERE id='$invoiceId'");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while($rowInv = mysqli_fetch_assoc($Invoice)){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $NewFeeId = $rowInv['id'];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql = mysqli_query($conn,"SELECT SUM(fee) as total FROM invoices WHERE id='$invoiceId'");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$row = mysqli_fetch_array($sql);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sum = $row['total'];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $total_price += $row[‘fee’];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; }}&nbsp; &nbsp; &nbsp; $BalanceToPay =&nbsp; $total_price - $balance_price;&nbsp; &nbsp; &nbsp; // you can keep for your data&nbsp;&nbsp; &nbsp; &nbsp; $grandBalanceForAllStudent += $BalanceToPay ;&nbsp; &nbsp; &nbsp;echo'<br>'.$exam.':' . $BalanceToPay ;&nbsp; &nbsp; &nbsp;&nbsp;}?>