Totaling a filtered field from a MySQL database......

[Tasumos]

Hi there!

 

I'm pretty new to php so I figure I'm probably doing something stupid here!

 

I'm trying to make a little game, it is going pretty well so far, and I thought I'd be adventurous and add a second table to hold "alliances".

 

I want to be able to display an alliances total "population", which is one of the stats each player has.

 

This is what I have come up with so far:

 

  $allianceplayers="SELECT population from game_users where allianceid='$alliance'";
  $allianceplayers2=mysql_query($allianceplayers) or die("Could not get alliance stats");
  $allianceplayers3=mysql_fetch_array($allianceplayers2); 
  $totalpop=0;
  foreach ($allianceplayers3 as &$value) {
     $totalpop=$totalpop+$value;
  }

 

When I input the query into phpmyadmin, it correctly shows two boxes, each one listing the population of one of the players in the alliance, so it seems MySQL understands what I am trying to do.

 

When I look at the contents of the array $allianceplayers3 with print_r($allianceplayers3) however, I get:

 

Array ( [0] => 199617 [population] => 199617 ) 

 

Where 199617 is the population of the first player in the alliance. This means my $totalpop becomes double the first player in the alliance, and never shifts from that, even if there is only 1 in there....

 

I hope I am explaining this right....

 

 

What should I be doing?!

 

Thanks in advance!!

 

 

[lonewolf217]

this will help

 

http://www.w3schools.com/sql/sql_func_sum.asp

[Tasumos]

Wow, here is me trying to be all complex!!!

 

Thanks!!!

[lonewolf217]

well its what i would have done if i didnt know about the SUM() function

[Tasumos]

I got it all working perfectly and managed to avoid hanging myself

 

In case anyone strolls past this thread in the future:

 

  $allianceplayers="SELECT SUM(population) AS totalpop FROM game_users WHERE allianceid='$alliance'";
  $allianceplayers2=mysql_query($allianceplayers) or die("Could not get alliance stats");
  $allianceplayers3=mysql_fetch_array($allianceplayers2); 
  $totalpop=$allianceplayers3[totalpop];

 

I don't think I'm doing it in the most efficient way - no need to create an array for a single variable! - but I have three players, two of which are me right now, so I don't think that matters - all I care about is that it works!!

 

Thanks again wolf!