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
