PHP, mysql - get number of rows with most of a collumn and sort descending
Posted on 2007-04-04
I am writing my own CMS - involved is a part where users can review different 'affiliates'.
The table called 'comments' has the following structure:
commentid affiliateid name affiliate rating comment email date ip status
1 1 Bob Allposters 5 Posters,etc,etc firstname.lastname@example.org 2007-04-01 22:08:02 192.0.0.1 L
status can be either L (listed) or W (waiting)
I need the code so that I can generate a table of 'most reviewed' affiliates. The affiliateid with the most reviews which have status 'L' are at the top, descending to the least, limited to 10. This table will have 2 collumns, which are: the affiliate name (e.g. allposters) and the number of reviews.
I hope I have made what I am looking for clear.
Please ask if you need more clarification.