在mysql SELECT中运行总计

Question

Thank you for all the examples and help for the running totals. Apparently I have missed something:

<?php
include 'connect.php';
$tdebit=0;
$tcredit=0;
$sql = <<<SQL
SELECT  ...., ($tdebit=$tdebit+debit) AS tdebit,
($tcredit=$tcredit+credit)  AS tcredit  
FROM accounting
SQL;

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
$items = array();
while($row = $result->fetch_assoc()){   
    array_push($items, $row);
}
echo json_encode($items);
mysqli_close($con);
?>

The result is 1 if a value in the field (credit/debit) and 0 if the value is zero. Any 'SET' or ':=' is not supported, the query is just not executed.

Thanks for any help, JPB

Answer 1

For the running total, you need a variable in the database, not the application layer.

So:

SELECT ....,
      (@tdebit := @tdebit + debit) AS tdebit,
      (@tcredit := @tcredit + credit) AS tcredit  
FROM accounting CROSS JOIN
     (SELECT @tcredit := 0, @tdebit := 0) params
ORDER BY ??;

Normally, when calculating a running total, you want the accumulation to be in a particular order. SQL result sets are not guaranteed to be in a particular order unless you specify an ORDRE BY clause. The ?? is for you to insert the appropriate column.