I am developing a report using php and mysql. My problem is that i want to display a sum of columns data on per page like the following format.
Page 1 of 3
-------------------
col1 | col2 | col3|
-------------------
abc 100.01|15.20|
abc 200.05|20.20|
-------------------
sum |300.06|35.40|
page 2 of 3
-------------------
col1 | col2 | col3|
-------------------
abc 100.01|15.20|
-------------------
sum |100.01|15.20|
and so on.....
Try with LIMIT that includes with offset of the current page like
SELECT SUM(col2),SUM(col3)
FROM myTable
LIMIT offset,limit
And foreach page offset will be changed.
You can set a parameter for calculate the sum in the loop. After ending the loop, you can echo it.
$total = 0;
for($i=1;$i<=$count;$i++)
{
//$val is an array, in which result is stored
$total = $total + $val[$i];
}
echo $total;
You can do this totally from mysql, using the following trick:
SELECT t.ID, SUM(t.col1), SUM(t.col2), SUM(t.col3)
FROM
(SELECT ID, col1, col2, col3 FROM mytable LIMIT 0,3) as t
GROUP BY t.ID WITH ROLLUP
Where LIMIT 0,3 is the LIMIT offset,limit of your pagination.