[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!


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

Reply via email to