动态计算php mysql结果总和

Question

I have a table with product price model cost stock etc, to make it easier I calculate the total customer pay for for each product like this

<?php echo number_format($show['quantity'] * $show['product_price'],0,',','.'); ?>

I need to show the total sum of this calculation but as you can see they are calculared in PHP real time. Is there a way to do it?

Here is the complete code

<?php
$result=mysqli_query($database,"SELECT * FROM `products` order by `category` ASC");
$rows=mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
?>

<table class="sales">

<tr>
<td>Quantity</td>
<td>Product Cost</td>
<td>Customer Pays</td>
</tr>        

<?php if($rows){$i=0;while($show=mysqli_fetch_assoc($result)){?>

<tr>
<td><?php echo number_format($show['quantity'],0,',','.'); ?></td>
<td><?php echo number_format($show['product_cost'],0,',','.'); ?></td>
<td><?php echo number_format($show['quantity'] * $show['product_cost'],0,',','.'); ?></td>
</tr>

<?php }}?>
</table>

TOTAL CUSTOMER PAY FOR ALL PRODUCTS = EXAMPLE $10.234

If there are 5 different products in the results with different prices and different Customer Pays I need to sum all those Customer Pays and show here

<?php }else{?> 

No products to show

<?php }?>

EDIT: SOLVED

The solution is

<?php
$count = mysqli_query($database, "SELECT SUM(stock * cost) AS totalPaid
FROM products");
while($total = mysqli_fetch_assoc($count)){
     echo number_format($total['totalPaid'],0,',','.');} ?>

Thanks to the correct answer of McAdam331

Answer 1

You can add the SUM as part of your SQL query:

SELECT SUM(quantity * product_cost) AS totalPaid
FROM myTable
GROUP BY customer;

I'm just guessing you'd group on customer based on the clues in your question, but you can change that to whatever you need.