One thing I would add to Jay's out loud thoughts... Always consider alternative approaches. Using a single query and calculating the totals at the same time is fine if you have a small dataset, or (as in this case) are producing page totals (I'm assuming the dataset is paged to fit all on one page, so restricting the number of rows to 20, say).
I had a situation where I had to report a bunch of stuff all at once, like 600 or 700 rows, and had to total 7 or 8 columns. In that case, I found it was far quicker to have separate queries for the row data and the totals, because the database was far quicker at totalling the stuff than the PHP code was. Now, where the cut off is I'm not sure. There are a huge number of factors which would be involved (size of dataset, choice of database, number of totals - and calculations required for these totals etc). I guess knowing plenty of alternatives - or at least understanding what you're doing enough to be able to think of alternatives - and thinking about stuff rather than just doing it the way you always did are the key points here. Just my tuppence... Richy ========================================== Richard Black Senior Developer, DataVisibility Ltd - http://www.datavisibility.com Tel: 0141 951 3481 Email: [EMAIL PROTECTED] -----Original Message----- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: 06 September 2002 16:33 To: [EMAIL PROTECTED] Subject: [PHP] Calculating Totals from table columns without a second query to the DB [thinking out loud] For small result sets where I am looking for column totals I generally issue 2 queries to the database, one for the data and one for the totals. This works fine, but for some things the database returns 100's or 1000's of records. To make this easier to use I "page" the records, showing an appropriate number of records for each page. The records for each page returned like so (normal); while($row = mysql_fetch_object($result)){ print("<td>" . $row->value . "</td>\n"); print("<td>" . $row->another_value . "</td>\n"); } The PHB would now like a "totals per page" and a "grand totals". Easy enough with 3 queries each time the page is called, one for the records, one for the page totals, (using proper LIMIT queries) and one for the grand totals, but awfully inefficient and intensive. I suppose I could do something like this; while($row = mysql_fetch_object($result)){ print("<td>" . $row->value . "</td>\n"); print("<td>" . $row->another_value . "</td>\n"); $value1 = $value1 + $row->value; $value2 = $value2 + $row->another_value; } [/thinking out loud] In the process of typing this out I of course realized that this would work very well. Even if the database contains NULL values they are treated as zero. This gets me back to 2 queries, one of which only has to be issued once (the one for the grand totals, the results can be held in variables and echo'd or printed as needed). It also seems to be very efficient as the $value variables will only be doing a one time math operation each time through the while loop. For smaller results sets all on one page the same type of operation could be used for the grand totals as well, working the whole report down to a single query. I use a lot of crosstab queries where totals are needed along the bottom or right side, you can only do one within the original query. The other totals are left to another query ... usually. This should be a lesson to us all, we all try to over-compicate the issue sometimes. Usually a look at the docs/manual/FAQ/other-text-intensive-method-of-delivering-information will deliver the solution to a problem while some thought slowed to a reasonable speed (such as me typing out the problem above) will also allow natural logic to occur, producing a solution. Break down the process into managable portions, solving each portion before moving on to the next. Challenge; Can this be made as simple for rows too? Peace and I HTH someone else! Jay Hard work has a future payoff. Laziness pays off NOW. ***************************************************** * Texas PHP Developers Conf Spring 2003 * * T Bar M Resort & Conference Center * * New Braunfels, Texas * * Contact [EMAIL PROTECTED] * * * * Want to present a paper or workshop? Contact now! * ***************************************************** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
