Hi Jay,
MySQL deserves some more respect. It's language is quite potent. Have you had a look on COUNT()? It is suited well for... well, counting. I would like to be able to sum up a collumns values already in MySQL. The following query will sum up all values of the column 'id': SELECT @idsum:=(IFNULL(@idsum, id)+id), id FROM yourtable; Now a question to the PHP/MySQL experienced: Why does this type of query not work in combination with PHP? I am using query above as $query and do the following standard procedure: $result = mysql_query($query); while ($row = mysql_fetch_row($result)) { echo join(", ", $row) . "<br>"; }; But MySQLs variable seems to be reset for each row. Is there any other access method on a query that can cope with it properly? Timo Am Freitag den, 6. September 2002, um 17:32, schrieb Jay Blanchard: > [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