Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jul 2007
    Location
    Scotland
    Posts
    134
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Problem with stats table

    Hi Guys,

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

    PHP Code:
         $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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •