Mutley Posted April 4, 2007 Share Posted April 4, 2007 I'm wanting to count how many "1" there are in a mysql table under a column called "xyz" then loop it to display the top 5 users (user_id) who has the mosts "1"s My database table is like this: user_id | xyz 1 | 1 | 1 2 | 1 2 | 1 So then on my page it shows: User: | XYZs: 2 | 2 1 | 1 I hope that makes sense, I don't know how to loop a SELECT COUNT in this way. Quote Link to comment Share on other sites More sharing options...
monk.e.boy Posted April 4, 2007 Share Posted April 4, 2007 select count(id) from table where xyz = 1 group by user_id I think the above will do it. The group by is what you want. Also google 'mysql having' monk.e.boy Quote Link to comment Share on other sites More sharing options...
Mutley Posted April 4, 2007 Author Share Posted April 4, 2007 So something like this: $sql = "SELECT COUNT(*) FROM table WHERE xyz = '1' GROUP BY user_id"; $result = mysql_query($sql); echo $result; Except how do I make it loop (and work, lol)? Quote Link to comment Share on other sites More sharing options...
Mutley Posted April 4, 2007 Author Share Posted April 4, 2007 ??? Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted April 4, 2007 Share Posted April 4, 2007 $query = "SELECT userid, SUM(xyz) AS hits FROM table_name GROUP BY userid ORDER BY hits DESC"; $result = mysql_query($query) or die(mysql_error()); echo ' <table> <tr> <td>Userid</td> <td>Count</td> </tr>'; while ($row = mysql_fetch_assoc($result)) { echo ' <tr> <td>' . $row['userid'] . '</td> <td>' . $row['hits'] . '</td> </tr>'; } Quote Link to comment Share on other sites More sharing options...
Mutley Posted April 4, 2007 Author Share Posted April 4, 2007 Where do I state only if: xyz = '1' ...though? Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted April 4, 2007 Share Posted April 4, 2007 if there is no value for xyz, it won't be added to the sum...conversely, if it has a value more than 1, that value will be added to the sum. To answer your question...the same place you always would....after the FROM statement, before the GROUP BY statement. Quote Link to comment Share on other sites More sharing options...
Mutley Posted April 4, 2007 Author Share Posted April 4, 2007 $query = "SELECT userid, SUM(xyz) AS hits FROM table_name WHERE xyz = '1' GROUP BY userid ORDER BY hits DESC"; Doesn't appear to work, just says "0". Quote Link to comment Share on other sites More sharing options...
monk.e.boy Posted April 5, 2007 Share Posted April 5, 2007 Yeah well, SQL is tricky. monk.e.boy Quote Link to comment Share on other sites More sharing options...
Mutley Posted April 5, 2007 Author Share Posted April 5, 2007 Still unable to get this to work. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.