CODEWITHSUNDEEP
phpmysqlsum
amirulfaizin
amirulfaizin

Reputation: 15

How to add up values in the table row which is retrieved from MySQL?

What I want to is add values in a table row retrieved from MySQL.

This is my PHP file:

<?php

mysql_connect('localhost','root','');
mysql_select_db('accounts');
$sql= "SELECT * FROM users1";
$list=mysql_query($sql);

?>
<html>
<head>
<title>Welcome to ScanNShop</title>
</head>
<h1>Welcome to ScanNShop</h1>

<body>

<table width="600" border="1" cellpadding="1" cellspacing="1">
<tr>

<th>No.</th>
<th>Name</th>
<th>Price</th>
<tr>

<?php

while ($users1=mysql_fetch_assoc($list)) {

echo "<tr>";

echo "<td>".$users1['pid']."</td>";

echo "<td>".$users1['name']."</td>";

echo "<td>".$users1['price']."</td>";

}

?>

</table>
</body>
</html  >

This is the output from the PHP file.

No. Name    Price
1   bread   2.00
2   milk    2.00 
3   janabab 6797994.00
4   jajajsh 846494.00

I want to add up all the price and display an echo "Total:" thetotal

Upvotes: 1

Views: 4603

Answers (3)

Gerjan Vlot
Gerjan Vlot

Reputation: 36

I kept your code easy and add the change's I could give you an other code but this is easy to understand if your learning php

I added a comment by the change's

mysql_connect('localhost','root','');
mysql_select_db('accounts');
$sql= ('SELECT * FROM users1');
$list=mysql_query($sql);

?>
<html>
<head>
<title>Welcome to ScanNShop</title>
</head>
<h1>Welcome to ScanNShop</h1>

<body>

<table width="600" border="1" cellpadding="1" cellspacing="1">
<tr>
<th>No.</th>
<th>Name</th>
<th>Price</th>
</tr>

<?php
 //set total to zero
$total=0;
while ($users1=mysql_fetch_assoc($list)) {

echo "<tr>";

echo "<td>".$users1['pid']."</td>";

echo "<td>".$users1['name']."</td>";

echo "<td>".$users1['price']."</td>";
//set total to the price + the previous total
$total = $users1['price']+$total;

echo "</tr>";

}
//display total
echo '<tr><td></td>';
echo '<td>Total:</td>';
echo '<td>'.$total.'</td></tr>';

?>

</table>
</body>
</html  >

Upvotes: 1

J Jorgenson
J Jorgenson

Reputation: 1499

Another way to sum data rows across is within the SQL query itself by using GROUP BY .. WITH ROLLUP (doc).

Example:

SELECT Number, Min(Name) AS Name, SUM(amount) AS Price
  FROM grocery_list
 GROUP BY Number WITH ROLLUP

Will produce the output:

Number  Name      Price
1       bread     2.00
2       milk      2.00 
3       janabab   6797994.00
4       jajajsh   846494.00
NULL    bread     7644492.00

The Line with NULL is the MIN(name), SUM(Price) across ALL records for the group 'Number'. You can then filter your output based on which field is NULL to determine how to display.

Upvotes: 1

mochalygin
mochalygin

Reputation: 742

May be you need just to sum results in special variable?

$sum = 0;
while ($users1=mysql_fetch_assoc($list)) {
    $sum += $users1['price'];

    echo '<tr>';
    echo '<td>'.$users1['pid'].'</td>';
    echo '<td>'.$users1['name'].'</td>';
    echo '<td>'.$users1['price'].'</td>';
    echo '</tr>';
}

echo '<tr>';
echo '<td></td>';
echo '<td>Total</td>';
echo '<td>'.$sum.'</td>';
echo '<tr>';

Do not use double quotes (") for strings. Use single quotes ('); And do not use mysql_* functions, they are deprecated.

Upvotes: 0

Related Questions