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, 

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 
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?


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

Reply via email to