top 5 posters

[didgydont]

i have a site that i want to display the top 5 posters but i have no idea how or where to start

this is what i have to count a user post but i cant figure out how to display or sort 

 

$result3 = mysql_query("SELECT * FROM Xbox360 WHERE User='$user'")
$count = mysql_num_rows($result3);

 

i tried this but i dont think im even close

$query = "SELECT *, COUNT(Game) AS Game FROM Xbox360 GROUP BY User ORDER BY User DESC";
$result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result)){ 
//echo $row['User'].": ".$row['COUNT(name)']."<br />";
echo $row['User'].": ".$row['COUNT(Game)'] ;
} 

 

thank you for your time

[btherl]

If count(Game) is the value you want to find the top 5 of, you can do:

 

$query = "SELECT *, COUNT(Game) AS Game FROM Xbox360 GROUP BY User ORDER BY COUNT(Game) DESC, User";

 

The ", User" means sort by user after sorting by count(Game).  That acts as a tie breaker in case people have the same count.

 

The value resulting from that query will be called "Game", ie:

 

echo $row['User'].": ".$row['Game'] ;

 

The reason for the is you said "count(Game) AS Game".  The "as" gives a name to the count value.

[didgydont]

that returned "Invalid use of group function"

i started to make a function to try to do it but that method would be heaps easier if i could get it working

 

[btherl]

Hmm, try this.  Unfortunately i don't have mysql to test with so I can't test myself.

 

$query = "SELECT User, COUNT(Game) AS Game FROM Xbox360 GROUP BY User ORDER BY COUNT(Game) DESC, User";

 

That's a vanilla group by that ought to work.. if it gives an error, try "ORDER BY Game" instead of "ORDER BY COUNT(Game)"

[didgydont]

thank you very much i will book mark this for next time i ended up making a function to do the lot though

function topposters($display){
$usersresult = mysql_query("SELECT * FROM Users");
$array = array();
//$result3 = mysql_query("SELECT * FROM Xbox360");
while($row = mysql_fetch_array($usersresult)){
  $user = $row['username'];
    $result3 = mysql_query("SELECT * FROM Xbox360 WHERE User='$user'");
    $count = mysql_num_rows($result3);
   //$array[] =  "Count => $count User => $user";
    $array["$user"] = $count ;

} 
arsort($array);
//$topposters = print_r($array);
//return "$topposters";
$i = 0;
Echo "<br><br><br><SPAN class='kinglahead'>Top $display Posters</SPAN><br>";
foreach($array as $key => $value) {$i++;
  if ($i<=$display){echo "$key(<a href='index.php?poster=$key'>$value</a>) ";}
    }
}