I am trying to post the totals of a currency column as the last row in a table of paginated results. So, I want the totals to display only on the last page of the result set, and not as the last row on each page of the paginated results.
Any tricks to this? I can get the results to display correctly, but they show up at the bottom of every page, and if I pull that row out of the php loop, then it doesn’t display at all.
I’ve considered a query of a query (although can’t do this in php?), or storing the original result in an array that can reused, but I am very new to php and have very rudimentary knowledge of this language. I have purchased and read the Sitepoint Simply SQL and MySQL and PHP books, am spending a lot of time on the forums and stackoverflow, but this issue is stumping me.
I’d appreciate any feedback as I am eager to learn PHP after so many years of building static HTML sites!
One of the points of pagination is so that you don’t run out of memory in a large data set. Storing as an array and then aggregating from there can fail. Why not just run a separate query to aggregate your data down and display at bottom only if your on the last page?
Kyle, thanks for the reply. I had considered running a separate query, but the totals are based on a subset of the records, dependent on the query sent by the form. And there are many factors, so the initial query is very long with many where conditions. It seems as if there should be a better way then run another huge query simply for one final sum. So it’s not just:
SUM(IFNULL(funding_provided,0) + IFNULL(other_funding,0)) as total_funds
but,
SUM(IFNULL(funding_provided,0) + IFNULL(other_funding,0)) as total_funds
where
15+ conditions
So, I was thinking there should be a way to ask for SUM of ‘funding’ based on the conditions of a previous query.
However - I’m hoping again it’s even easier because I already have the data available from the first query, that’s not the issue. It’s just asking for the result outside of the loop so it doesn’t display at the bottom of each page of results, but only at the end.
But, I’m still trying to wrap my head around how all this works, and even have problems with simple things like globals, etc, and having 2 queries on one page. 
So, I guess what you’re saying is to have 2 queries, but I’m trying to avoid that because there are many conditions, and that seems like a lot of extra code to pull data that already is available in the first query result set…
Can you supply an example of why there may be many conditions? The example you provided, to me, doesn’t seem like a true condition.
No problem. This is the form - http://dev.recreationaltrailsinfo.org/database/all_form.php
The where clause incorporates all the conditions of this form.
So it looks like you just want to sum up RTP Funds, Other Funds and Total Funds, for all pages, but only display at the end of the final paginated set?
It sounds like the multiple conditions you were referring to were actually on the search page itself. You can do something as simple as storing the final SQL query to a variable, and then querying from that SQL again as a subquery.
//gather conditions from POST, compile select statement to $SQL
if($CurrentPage == $PageCount) {
$Aggregate$SQL = "Select sum(RTP_Funds) as total_rtp_funds , sum(other_funds_ as total_other_funds , sum(total_funds) as total_total_funds from ($SQL)";
//run $Aggregate$SQL and display
}
This allows for the aggregation to be run on any query that had been built
Kyle, Thank you very much. It sounds like what i need to do. Store the Query to a variable and then query the result array. I will use your example and do what I can to make this work. Thanks for pointing me in the right direction! When I get a good result I will confirm my solution, but it may take some more trial and error on my part!
Well you wont be querying the result array, you’ll be recreating a query with the original query as a subquery (temp table). Where $SQL is a string and executed to MySQL. Ex: "$SQL = “select * from my table”;