RE: [PHP] Calculating Totals from table columns without a second query to the DB
[snip] 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: [/snip] Good point! Now as to your question, try this... SELECT idsum:=(IFNULL(idsum, id)+id) AS idsum, id FROM yourtable; When you output your query info output the value as $row-idsum; HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Calculating Totals from table columns without a second query to the DB
Hi Jay, you suggestion does not make any difference. The problem is that idsum seems to be destroyed for every row! Is there any other access method on a query that works properly or do I have to live with different results of the very same query asked directly in MySQL and asked through PHP? Timo Am Montag den, 9. September 2002, um 15:04, schrieb Jay Blanchard: [snip] 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: [/snip] Good point! Now as to your question, try this... SELECT idsum:=(IFNULL(idsum, id)+id) AS idsum, id FROM yourtable; When you output your query info output the value as $row-idsum; HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Calculating Totals from table columns without a second query to the DB
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
[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
RE: [PHP] Calculating Totals from table columns without a second query to the DB
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
RE: [PHP] Calculating Totals from table columns without a second query to the DB
Hi Jay,
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;
}
Challenge; Can this be made as simple for rows too?
thinking out loud :-)
?php
$rowTotals = array ();
while ($row = mysql_fetch_object($result)) {
$rowTotal = 0;
foreach ($row as $key=$val)
$rowTotal += $val;
// display whatever is needed from $row
$rowTotals[] = $rowTotal; // or echo it out to the last column of the
table
}
?
/tol
I don't usually use mysql_fetch_object so I'm not sure if you can walk
through it with foreach() though...
Cheers
Jon
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Calculating Totals from table columns without a second query to the DB
[snip]
Challenge; Can this be made as simple for rows too?
thinking out loud :-)
?php
$rowTotals = array ();
while ($row = mysql_fetch_object($result)) {
$rowTotal = 0;
foreach ($row as $key=$val)
$rowTotal += $val;
// display whatever is needed from $row
$rowTotals[] = $rowTotal; // or echo it out to the last column of the
table
}
?
/tol
[/snip]
:^]
Or based on the original query;
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;
}
you could add the following to the WHILE loop ...
print(td . ($row-value + $row-another_value) . /td\n);
... giving you another column with the row total. That way you could avoid
adding columns that do not contain numerical values if that is the case in
your result set. And I think that you would want to use mysql_fetch_array
for your example.
Jay
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
