How to retrieve column total when rows are paginat

问题:

I have a column "price" in a table and I used a pagination script to generate the data displayed. Now the pagination is working perfectly however I am trying to have a final row in my HTML table to show the total of all the price. So I wrote a script to do just that with a foreach loop and it sort of works where it does give me the total of all the price summed up together however it is the sum of all the rows, even the ones that are on following pages. How can I retrieve just the sum of the rows displayed within the pagination?

Here is the query..

SELECT 
purchase_log.id,
purchase_log.date_purchased,
purchase_log.total_cost,
purchase_log.payment_status,
cart_contents.product_name,
members.first_name, members.last_name,
members.email FROM purchase_log LEFT
JOIN cart_contents ON purchase_log.id
= cart_contents.purchase_id LEFT JOIN members ON purchase_log.member_id =
members.id GROUP BY `id` ORDER BY `id` DESC LIMIT 0,30";

回答1:

Just use MySQL function FOUND_ROWS(): http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

回答2:

You need to append LIMIT 0,20 to a mysql query this will only sum total the current pageinated displayed results.

I assume you use a LIMIT to pageinate the results, unless you array the data into a grid in which case you'll need to post code.

回答3:

What about using the mysql SUM function in your mySQL query? http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum

回答4:

First you need to initialize total to zero so that it can display the sum of the records that you are fetching with limit.

<?php 
$total = 0;
foreach ($purchase as $items) : 
$total += $items['purchase_log.total_cost']; 
endforeach; 
?>

Hope this helps.