Page 1 of 1

Displaying Sum() results to a webpage

Posted: Mon May 30, 2005 6:47 pm
by besbajah
Hi,

Please can somebody help me with my niggly issue please?

I want to display the SUM() value of a column and output it to a webpage.

I've been fiddling around with PHP for a week or two and have got my webpages to output rows and order those rows, using code as follows (relevant part): -

$result = mysql_db_query("interpre_music", "select * from Artist");

mysql_close();
?>

<?
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
print("{$row['Rank']}");
echo "<br>";
}
?>

HOWEVER,
When I change the actual query line itself to: -

$result = mysql_db_query("interpre_music", "select Sum(Rank) from Artist");

I don't get a result, just good old - Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.
Probably getting this error because I'm trying to put a single sum into a whole array, well I'm not sure.

Would someone have the modified code or an idea of how to display the SUM out onto a webpage?

Help Much Appreciated,
Bes.

Posted: Mon May 30, 2005 6:54 pm
by Burrito
try:

Code: Select all

select sum(rank) as thisrank from ...
then output "thisrank"

Posted: Mon May 30, 2005 7:22 pm
by besbajah
Sorry, I didn't quite get that code. I tried to put that statement into my query line but it didn't work.

I guess 'thisrank' is the new name for the column name right?
Or is it now a variable?

Sorry again, but that was a bit vague.
This is going a little too quickly for me. Thanks for your patience, please explain more.

Bes.

Posted: Mon May 30, 2005 7:35 pm
by Burrito

Code: Select all

$query = "select sum(rank) as thisrank from Artist";
$result = mysql_query($query);
if($row = mysql_fetch_assoc($result)){
  echo $row['thisrank'];
}
by using "as" you're setting an "alias" or "variable" for the result of the sum() function in MySQL. This is what you will use to output your result (as shown above).

also notice how I used "if" to create my results array as it should only return one row with the sum() function. If you put other items to select, and you anticipate that there will be more than one row returned, you should loop it over a while loop.

let me know if that's clear enough for you.

Posted: Mon May 30, 2005 8:13 pm
by besbajah
Hi there,

Yes, I got thouroughly confused. Firstly to be completely ho-ho honest, I was changing the name of the table in my query thinking I was naming the darned column; ok, I'll explain another time, it's been a long day.

Yes, I get your drift, the AS statement makes an effective variable that you can output later on. I tried it and it works.

select SUM(Rank) as ThisRank from Music

&

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
print("{$row['ThisRank']}");
}

check out: -
http://mmm.interpretacao.com/Music/TEST.php
- the ranking looks viable.

and you can see, I'm using the 'while' statement. I liked your comments about the 'If' statement though, and I'll have a play with that; as you say "if you just want to display one output". As far as the 'while' loop is concerned though, steady on now, steady on, not too fast.

Thanks very much for getting back to me on this post. Your help has been very, very useful. Best Wishes.

Bes.