Row wise Sum of SQL Database by PHP

Question

I want to sum of row & show the result in the same row as Remaining Column but i am getting same result (Sum of first row) in every row here is my PHP code

<?php
   $sub = mysql_connect ("localhost", "root", "");
   if (!$sub)
   {
   die('Could Not Connect:' .mysql_error());
   }
   mysql_select_db("tabsc",$sub);
   if (!empty($_POST))
   {
   $sql= "INSERT INTO fund (dat, sour, condi, amount, disburse, othe, expenditure)
   VALUES ('$_POST[dat]','$_POST[sour]','$_POST[condi]','$_POST[amount]','$_POST[disburse]','$_POST[othe]','$_POST[expenditure]')";
   header('location: funding.php');
   if (!mysql_query($sql, $sub))
   {
   die('Error' .mysql_error());}}       

  //start add sql column value on the top 
  $result = mysql_query('SELECT SUM(amount) AS amount FROM fund');
  $row = mysql_fetch_assoc($result);
  $sum = $row['amount'];

  $result = mysql_query('SELECT SUM(disburse) AS disburse FROM fund');
  $row = mysql_fetch_assoc($result);
  $sumd = $row['disburse'];

  $result = mysql_query('SELECT SUM(othe) AS othe FROM fund');
  $row = mysql_fetch_assoc($result);
  $sumo = $row['othe'];

  $result = mysql_query('SELECT SUM(expenditure) AS expenditure FROM fund');
  $row = mysql_fetch_assoc($result);
  $sume = $row['expenditure'];
  $remh=$sum-($sumd+$sumo+$sume);
//end add sql column value on the top 

   $resul=mysql_query("SELECT * FROM fund");
   echo "<table border='1'>
       <tr>
       <th></th>
       <th></th>
       <th>Total</th>
       <th>$sum</th>
       <th>$sumd</th>
       <th>$sumo</th>
       <th>$sume</th>
       <th>$remh</th>
       <th></th>
       <tr>
       <th>Date</th>
       <th>Source</th>
       <th>Condition</th>
       <th>Amount</th>
       <th>Disburse</th>
       <th>Other Payament</th>
       <th>Expenditure</th>
       <th>Remaining</th>
       <th>Delete/Edit</th>
       </tr>";

//Row Wise Sum (I think problem is here)
 $result =mysql_query('SELECT id, SUM( amount + disburse + othe + expenditure) AS remaining FROM fund GROUP BY id');
 $row = mysql_fetch_assoc($result);
 $sumrem = $row['remaining'];



   while ($row = mysql_fetch_array($resul))
   {
   echo '<tr>';
   echo '<td>' .$row['dat'].'</td>';
   echo '<td>' .$row['sour'].'</td>';
   echo '<td>' .$row['condi'].'</td>';
   echo '<td>' .$row['amount'].'</td>';
   echo '<td>' .$row['disburse'].'</td>';
   echo '<td>' .$row['othe'].'</td>';
   echo '<td>' .$row['expenditure'].'</td>';
   echo "<td>$sumrem</td>"; //Result will be into this columne
   echo '<td><a href="delete.php? id='.$row['id'].'">Del</a> || <a href="edit.php? id='.$row['id'].'">Edit</a>';
   echo '</tr>';
   }
   echo '</table>';
   mysql_close($sub);
   ?>

*"but i am getting same result (Sum of first row) in every row"* - What results are you getting now and what are the desired results? You should post your schema and values for this and possibly setup an mysql fiddle and the possibly the form for this. — Funk Forty Niner, Dec 29 '16 at 20:05
I am getting sum of the first row of the DB, Desired result will be the sum of each row in remaining column. thanks — sadi, Dec 29 '16 at 20:18
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 29 '16 at 20:19
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Dec 29 '16 at 20:19
Is `id` a unique column in the table? Using `SUM()` and `GROUP BY id` is useless in that case, since each group will just be 1 row. — Barmar, Dec 29 '16 at 21:16

score 0 · Answer 1 · answered Dec 29 '16 at 20:37

I think you need to run that query inside the loop to pass the ID to get the remaining per row, otherwise you would not know what specific row, don't rely on id order.
Try to minimize the number of calls to the database, I would personally select all then use a php expression to sum and get the grand totals and also get the remaining per row on that call as well.
If you make so many calls like that in separate transactions like getting the grand totals you are running into the risk of possibly having a user change an amount while you are getting the totals, thus displaying the wrong amount. So get all columns plus the remaining calculated column, then calculate grand totals in php

score 0 · Answer 2 · answered Dec 29 '16 at 21:22

0

If you want to get the sum of all rows, not just the first row, don't use GROUP BY.

$result =mysql_query('SELECT SUM( amount + disburse + othe + expenditure) AS remaining FROM fund');
$row = mysql_fetch_assoc($result);
$sumrem = $row['remaining'];

You can also combine all the other SELECT SUM() queries into a single query:

$result = mysql_query('SELECT SUM(amount) AS amount, SUM(disburse) AS disburse, SUM(othe) as othe, SUM(expenditure) AS expenditure FROM fund');
$row = mysql_fetch_assoc($result);
$sum = $row['amount'];
$sumd = $row['disburse'];
$sumo = $row['othe'];
$sume = $row['expenditure'];
$remh = $sum - $sumd - $sumo - $sume;
$sumrem = $sum + $sumd + $sumo + $sume;

answered Dec 29 '16 at 21:22

Barmar

741,623
53
500
612

Its showing the sum of column amount, disburse, othe, & expenditure in the remaining column and all row have same result. I am trying to add row value in the remaining column in same row, i have a unique id column with auto increment. – sadi Dec 30 '16 at 06:31
I couldn't really figure out what you were trying to do from the question, I made some guesses from your code. Are you trying to do a running total in the Remaining column? – Barmar Dec 30 '16 at 16:36

score 0 · Answer 3 · answered Dec 30 '16 at 17:47

Thanks every one... I got an idea & its working fine. after inputting data into DB i run this query in the table

$sql =mysql_query('UPDATE fund SET remaining = ( amount - (disburse + othe + expenditure))');

Then echo the result into the remaining column.

Row wise Sum of SQL Database by PHP

3 Answers3