help with come sql sorting

[sandrob57]

I am running an online game (lunarwars.net) and I need help. The players have asked to create alliance, and I have obliged.

 

I want to make a page where you can view each alliance, and have that sorted by how many players are in each alliance.

 

This is the database structure.

 

You have fusion_users table, and the fields we are working with are user_name, c_strength (alliance strength) and c_alliance (alliance name).

I have the script working, where it displays each alliance with however many members it has and its strength.

 

The problem is, I cant get the page to order by which alliance has the highest cumulative strength (each player in the alliance has strength, and each alliances strength is all its members strength added up).

 

this is the code I have so far, everything works except my sorting problem

 

      echo "<table align='center' cellpadding='0' cellspacing='1' width='600' class='tbl-border'>
<tr>
<td align='center' width='1%' class='tbl2' style='white-space:nowrap'><b>Alliance Name</b></td>
<td align='center' width='1%' class='tbl2' style='white-space:nowrap'><b>Members</b></td>
<td align='center' width='1%' class='tbl2' style='white-space:nowrap'><b>Strength</b></td>
</tr>\n";

      $result1 = dbquery("SELECT DISTINCT c_alliance FROM fusion_users HAVING c_alliance!='' ORDER BY c_alliance ASC, user_name LIMIT $rowstart,20");
  
      while ($data = dbarray($result1)) {

         $cell_color = ($i % 2 == 0 ? "tbl1" : "tbl2"); $i++;

	 $place = $i + $rowstart;

	 $alliance = $data['c_alliance'];
	 $alliance = str_replace(" ","%20",$alliance);

         echo "\n<td class='$cell_color'><a href=".BASEDIR."alliance.php?a_choice=$alliance>".$data['c_alliance']."</a></td>";

   				  $members = dbcount("(c_alliance)", "users", "c_alliance='".$data['c_alliance']."'");

         echo "\n<td class='$cell_color'>".number_format($members)."</td>";

			  $result = dbquery("SELECT * FROM ".$db_prefix."users WHERE c_alliance='".$data['c_alliance']."'");
	  
			  $strength = 0;
			  while ($data = dbarray($result)) {
			  $strength = $strength + $data['c_strength'];
			  }

         echo "\n<td class='$cell_color'>".number_format($strength)."</td></tr>";
      }

      echo "</table>\n";

   } else {

      echo "<center><br>Error. Please come back later.<br><br>\n</center>\n";

   }

 

[magic2goodil]

Easiest idea i have would be to change your code some and to add a field to your db that holds the total strength of all the users in the alliance.  Then you can just straight call it instead of running a strength +=  and such over and over every time u run this page.  I say just add the field and set up a place in your script that adds to / subtracts from the total alliance strength whenever a user joins / leaves the alliance.

[sandrob57]

Easiest idea i have would be to change your code some and to add a field to your db that holds the total strength of all the users in the alliance.  Then you can just straight call it instead of running a strength +=  and such over and over every time u run this page.  I say just add the field and set up a place in your script that adds to / subtracts from the total alliance strength whenever a user joins / leaves the alliance.

Strength changes on each user every day for every action they make  :-\

[magic2goodil]

Perhaps when their strength is changed it could run something to add up the new total of your alliance strength?

[sandrob57]

I really just need a way to sort by strength for each colon, I'm sure it isnt too difficult.

[JBS103]

Just a quick question, is the c_strength field the cumulative strength of the alliance or is it the member's own strength number?

 

For example is it something like this in the table??

PName | PStrength | PAlliance

PlayerA |    5        |      A

PlayerB |    6        |      A

PlayerC |    3        |      B

 

And you want it to display??

Alliance A : Strength = 11

Alliance B : Strength = 3

 

Because if it is that way, you should be able to get away with a query using SUM, Group by, and Order by.

 

[sandrob57]

Just a quick question, is the c_strength field the cumulative strength of the alliance or is it the member's own strength number?

 

For example is it something like this in the table??

PName | PStrength | PAlliance

PlayerA |    5        |      A

PlayerB |    6        |      A

PlayerC |    3        |      B

 

And you want it to display??

Alliance A : Strength = 11

Alliance B : Strength = 3

 

Because if it is that way, you should be able to get away with a query using SUM, Group by, and Order by

 

It is a member's own strength number that need to be added up for an alliance (exactly how you table shows it)

[JBS103]

Try something like this.

$query = "Select c_alliance, SUM(c_strength) from fusion_users GROUP BY c_alliance ORDER BY SUM(c_strength) DESC";
$results = dbquery($query);
while($row = mysql_fetch_array($results))
{
echo $row['c_alliance']." - ".$row['SUM(c_strength)']."<br />";
}

 

[sandrob57]

I get Invalid use of group functionInvalid use of group function from using:

 

$result1 = dbquery("Select c_alliance, SUM(c_strength) from fusion_users GROUP BY c_alliance ORDER BY SUM(c_strength) DESC");

[JBS103]

I get Invalid use of group functionInvalid use of group function from using:

 

$result1 = dbquery("Select c_alliance, SUM(c_strength) from fusion_users GROUP BY c_alliance ORDER BY SUM(c_strength) DESC");

Maybe that was my error, but the same query works for me on my table setup so I'm not sure what is causing it.

 

Try this maybe, GROUP BY c_alliance, c_strength ORDER BY SUM(c_strength) DESC

[sandrob57]

Strange. I get the same error with this:

 

      $result1 = dbquery("SELECT c_alliance, SUM(c_strength) FROM fusion_users GROUP BY c_alliance, c_strength ORDER BY SUM(c_strength) DESC");

 

I tried some variations (moving commas, adding quotes) but no luck yet. Ill keep trying. Let me know if you think of something else.

[sandrob57]

I feel I am getting warmer with this:

 

      $result1 = dbquery("SELECT DISTINCT c_alliance, SUM(c_strength) FROM fusion_users WHERE c_alliance!='' GROUP BY c_alliance ORDER BY SUM(c_strength) DESC, c_alliance LIMIT $rowstart,20");

 

But I still get:

 

Invalid use of group functionInvalid use of group function

[Barand]

try

$result1 = dbquery("SELECT c_alliance, SUM(c_strength) as total 
                          FROM fusion_users 
                          GROUP BY c_alliance 
                          ORDER BY total DESC");

[JBS103]

      $result1 = dbquery("SELECT DISTINCT c_alliance, SUM(c_strength) FROM fusion_users WHERE c_alliance!='' GROUP BY c_alliance ORDER BY SUM(c_strength) DESC, c_alliance LIMIT $rowstart,20");

As far as I know, there are some issues with using WHERE and ORDER BY.

 

I think Barand is the most on track, at the moment.

[sandrob57]

Could I use HAVING instead of WHERE? I want to prevent people who have not chosen an alliance (people with nothing in the c_alliance field) to show up on the list at all.

[sandrob57]

This works perfectly - THANKS! 

 

      $result1 = dbquery("SELECT DISTINCT c_alliance, SUM(c_strength) AS total FROM fusion_users WHERE c_alliance!='' GROUP BY c_alliance ORDER BY total DESC LIMIT $rowstart,20");

[Barand]

GROUP BY gives distinct values so don't need DISTINCT when you use GROUP BY.

 

WHERE should be fine. You'd use HAVING when you need a condition on the aggregated total

(eg ... HAVING total > 50)

 

 

$result1 = dbquery("SELECT c_alliance, SUM(c_strength) as total 
                          FROM fusion_users
                          WHERE c_alliance <> ''
                          GROUP BY c_alliance 
                          ORDER BY total DESC");