SQL - Top 5 power guilds, ascending order ?

[psilocibe]

This shows the level of guilds,
I'm doing a top 5 guilds level, how do I sort in ascending order.
do this work in sql?

while($x<=5) {
  $sumav = $SQL->query("SELECT AVG(`level`) AS `level_avg` FROM `players` LEFT JOIN `guild_membership` AS `gm` ON `gm`.`player_id` = `players`.`id` WHERE `gm`.`guild_id` = ".$x."")->fetch();
  $x++;
  echo round($sumav['level_avg']);
  echo '<br>';
}

Sry my english.

 

[Cornex]

ORDER BY `level` ASC LIMIT 1;

Edit, saw now for guilds.. Use this as an example

 

[psilocibe]

ORDER BY `level` DESC LIMIT 1;

Edit, saw now for guilds.. Use this as an example

Tested, not work. Test you.

while($x<=5) {
"SELECT AVG(`level`) AS `level_avg` FROM `players` LEFT JOIN `guild_membership` AS `gm` ON `gm`.`player_id` = `players`.`id` WHERE `gm`.`guild_id` = ".$x." ORDER BY `level` ASC LIMIT 1"
$x++;
echo round($sumav['level_avg']);
echo '<br>';
}

Up.

Did not work and other tests, increased the level of the guild but not ordered.

$x=1;
while($x<=2) {
  $sumav = $SQL->query("SELECT AVG(`p`.`level`) AS `avg_level`, `g`.`name` AS `name` FROM `players` p LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = ".$x." WHERE ".$x." > 0 GROUP BY `name` ORDER BY `avg_level` DESC, `name` ASC LIMIT 0, 5")->fetch();
  $x++;
  echo round($sumav['avg_level']);
  echo '<br>';
}

Result:

693
727

Guild Level 727 is in front of the 693

My script.

echo'<form action="?subtopic=accountmanagement" method="post"><div class="Themebox2" style="background-image:url('.$layout_name.'/images/themeboxes/admin/login2.gif);"><br><tr bgcolor="#f1e0c6">
        <td><table width="100%" cellspacing="0" cellpadding="0" border="0">        
    <tbody><tr><td></font></b></td></tr></tbody></table></td></tr><br><font color="#234234"><b><center>';
  $x=1; 
  while($x<=2) {   
  $sumav = $SQL->query("SELECT AVG(`p`.`level`) AS `avg_level`, `g`.`name` AS `name` FROM `players` p LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = ".$x." WHERE ".$x." > 0 GROUP BY `name` ORDER BY `avg_level` DESC, `name` ASC LIMIT 0, 5")->fetch();
  $x++;
  echo '<br>';
  echo round($sumav['avg_level']);
//  echo $sumav['name'];
  }
  echo'<br><br><br><br><br><br></form>';

 

[psilocibe]

Anyone have any ideas?

 

[Chris]

SELECT g.name AS guild, ROUND(AVG(p.level)) AS average FROM guilds AS g LEFT JOIN guild_membership AS gm ON gm.guild_id = g.id LEFT JOIN players AS p ON p.id = gm.player_id GROUP BY g.id ORDER BY average DESC LIMIT 5;

 

[psilocibe]

SELECT g.name AS guild, ROUND(AVG(p.level)) AS average FROM guilds AS g LEFT JOIN guild_membership AS gm ON gm.guild_id = g.id LEFT JOIN players AS p ON p.id = gm.player_id GROUP BY g.id ORDER BY average DESC LIMIT 5;

I changed the DESC to ASC but the result does not change. He must calculate the level of all the guilds and show which is the largest.

The guild level is calculated by adding the level of all members and dividing by the number of members.

If you got it can show your script?

 

[Chris]

<?php

$topGuilds = $SQL->query('SELECT g.name AS guild, ROUND(AVG(p.level)) AS average FROM guilds AS g LEFT JOIN guild_membership AS gm ON gm.guild_id = g.id LEFT JOIN players AS p ON p.id = gm.player_id GROUP BY g.id ORDER BY average DESC LIMIT 5;')->fetchAll();

foreach ($topGuilds as $guild) {
    echo '<p><strong>'.$guild['guild'].'</strong> - '.$guild['average'].'</p>';
}

 

[psilocibe]

Fixed.

It worked perfect. Thank you very much for your help.

 

[psilocibe]

Look man the final Result.

http://otland.net/threads/guild-level-war-site.218883/