Problem with stats table

Hi Guys,

im making a kind of statistics table where the highest voted user profiles are displayed this code works:

$query5 = "SELECT `id`,`file_name`,`total`, COUNT(*) AS `count` FROM `membership` GROUP BY `total` ORDER BY `count` DESC LIMIT 10";
$result5 = mysql_query($query5) or die (mysql_error());

echo '<table width="60%" border="1" bordercolor="#000000" cellpadding="2" cellspacing="0" />
<tr>
<th bgcolor="#004E98" colspan="2"><font color="#ffffff">Top 10 Rated Profiles</font></th>
</tr>
<tr>
<td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>File Name</b></font></td><td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>Average Rating</b></font></td>
</tr>';

while ($row = mysql_fetch_array($result5)) {

// get the usernames clickable.../////////////////////////////////////////////

$file = $row['file_name'];
$total = $row['total'];
$id_3 = $row['id'];

if($total == "") {


} else {

echo '<tr><td align="center"><a href="userdetails.php?id='.$id_3.'">'.$file.'</a></td><td align="center">'.$total.'</td></tr>';

}

}

echo "</table><br /><br />";

i initially used int(10) for the total table but it had a 0 in it as the default value so when i tried to count statistics it kepts adding 0 as a value, then i changed it to varchar(10) which by defualt show a blank space, i can't get the display to show highest number first it's all jumbled

12
24
3

etc even with DESC in the query can anyone see any errors i have made?

cheers guys

Graham

You can't specify a column name in the SELECT part of a query with a GROUP BY if that column isn't in the GROUP BY clause. MySQL allows it, but it's incorrect. You are selecting 'id' and 'file_name' but neither are in the GROUP BY clause, so it's anyone's guess what you'll get back. I'm not even sure why you are using GROUP BY; if the 'total' column contains the count that determines your top 10 list, simply select 'total' and ORDER BY 'total'. Right now you are ordering by 'count', which is the number of rows with the same value in 'total' for each result.

Use the INT type and include "WHERE total > 0" in your query. Better yet, don't store totals-- calculate totals inside your queries on the fly.