[SOLVED] Find the sum of 1 database column for every user

[MasterACE14]

basically, I have this code:

<?php

function economy() {

    $sql = "SELECT * FROM cf_users";
    $rs = mysql_query($sql) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());
    $row = mysql_fetch_assoc($rs);

}

?>

 

thats just the starting point, there is a column in cf_users called 'ammo'

 

I want to add up the total of 'ammo' for every single entry in `cf_users` , but I'm not sure as to whether this is done using PHP or is MySQL based, I assume MySQL.

 

so if you don't understand what I'm asking. Take a look at this example:

 

user 1 - Ammo = 98

user 2 - Ammo = 57

user 3 - Ammo = 122

 

total of ammo = 277

 

I want to do that with MySQL for every user.

 

any help is greatly appreciated.

 

Regards ACE

[TheBigRedStapler]

I'm pretty sure MySQL has a sum() function.. http://www.tizag.com/mysqlTutorial/mysqlsum.php

 

HTH

[mmarif4u]

if i understand you correctly you can use sum in mysql query.

for exmaple:

select sum(ammo) from cf_users

 

Edit: TheBigRedStapler, you won.

[MasterACE14]

ok, I'll give that a try. Thanks guys.

[dannyb785]

if i understand you correctly you can use sum in mysql query.

for exmaple:

select sum(ammo) from cf_users

 

Edit: TheBigRedStapler, you won.

 

To refine this guy's post just a bit, do the following:

select sum(ammo) as total from cf_users

 

 

then, after you do mysql_fetch_array on the result of the query(for example, $row = mysql_fetch_array($result)), you'll have the sum in a the variable $row['total']. Basically whatever you do something as, is how you will reference it from then on(whether you need to restate the name in the query or need to use it as a variable after being "fetched"

[MasterACE14]

this is what I have now.

 

<?php
function economy_percent() {
// users
    $sql = "SELECT SUM(`ammo`),SUM(`bank`),SUM(`money`),SUM(`titanium`),SUM(`uranium`) as total FROM cf_users";
    $rs = @mysql_query($sql) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());
    $row = mysql_fetch_assoc($rs);
// global
$sql1 = "SELECT SUM(`treasury`) as total FROM cf_global";
    $rs1 = @mysql_query($sql1) or die('Query:<br />' . $sql1 . '<br /><br />Error:<br />' . mysql_error());
    $row1 = mysql_fetch_assoc($rs1);

$economy = (($row['ammo'] + $row['bank'] + $row['money'] + $row['titanium'] + $row['uranium']) / 5);
$treasury = $row1['treasury'];
$economy_percentage = (($treasury / $economy) * 100);

return $economy_percentage;

};
?>

 

however when echo'd i receive this error:

Warning: Division by zero in /home/ace/public_html/conflictingforces/functions.php on line 104

 

and it echo's:

0

 

Any ideas?

[dannyb785]

whats on 104?

[MasterACE14]

oh sorry, I forgot to comment that line.

 

<?php
function economy_percent() {
// users
    $sql = "SELECT SUM(`ammo`),SUM(`bank`),SUM(`money`),SUM(`titanium`),SUM(`uranium`) as total FROM cf_users";
    $rs = @mysql_query($sql) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());
    $row = mysql_fetch_assoc($rs);
// global
$sql1 = "SELECT SUM(`treasury`) as total FROM cf_global";
    $rs1 = @mysql_query($sql1) or die('Query:<br />' . $sql1 . '<br /><br />Error:<br />' . mysql_error());
    $row1 = mysql_fetch_assoc($rs1);

$economy = (($row['ammo'] + $row['bank'] + $row['money'] + $row['titanium'] + $row['uranium']) / 5);
$treasury = $row1['treasury'];
$economy_percentage = (($treasury / $economy) * 100); // line 104

return $economy_percentage;

};
?>

[dannyb785]

Dude, remember what i said about using the AS in your query? First off, you aren't grabbing any data from row['ammo'], $row['bank'], $row['money'], $row['titanium'], or $row['uranium'].

 

Secondly, I wasn't clear about the AS. you need to have and AS for each variable you're getting. So in your case, you'd do "SELECT SUM(ammo) as ammosum,SUM(bank) as banksum, etc..."

 

then after your $result is fetched, you have $row['ammosum'], $row['banksum'], etc...

 

So basically, you're dividing by zero because $economy has no value(so by default, a zero is used) since there are no values in $row['ammo] and so on.

[mmarif4u]

Yup u have to use it like this way as danny mention above:

"SELECT SUM(ammo) as ammosum,SUM(bank) as banksum, etc..." 

Not like:

"SELECT SUM(`ammo`),SUM(`bank`),SUM(`money`),SUM(`titanium`),SUM(`uranium`) as total FROM cf_users";

[sasa]

try

<?php
function economy_percent() {
// users
    $sql = "SELECT SUM(`ammo`) AS s_ammo,SUM(`bank`) AS s_bank,SUM(`money`) AS s_money,SUM(`titanium`) AS s_titanium,SUM(`uranium`) as s_uranium FROM cf_users";
    $rs = @mysql_query($sql) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());
    $row = mysql_fetch_assoc($rs);
// global
$sql1 = "SELECT SUM(`treasury`) as total FROM cf_global";
    $rs1 = @mysql_query($sql1) or die('Query:<br />' . $sql1 . '<br /><br />Error:<br />' . mysql_error());
    $row1 = mysql_fetch_assoc($rs1);

$economy = (($row['s_ammo'] + $row['s_bank'] + $row['s_money'] + $row['s_titanium'] + $row['s_uranium']) / 5);
$treasury = $row1['total'];
$economy_percentage = (($treasury / $economy) * 100); // line 104

return $economy_percentage;

};
?>

 

[MasterACE14]

yeah, i just changed it then the way danny said, and its working now! Thankyou so much danny, mmarif4u, TheBigRedStapler and sasa.

 

Much appreciated! 

 

Regards ACE