Ordering by top commented.

Question

How would I list a page of the top commented pages on the site with PHP and mysql?

The database is set up sort of like this:

page_id | username | comment | date_submitted
--------+----------+---------+---------------
      1 |  bob     | hello   | current date
      1 |  joe     | byebye  | current date
      4 |  joe     | stuff   | date
      3 |  mark    | this    | a date

How would you query it so that it orders them by top commented pages?

Here is a simple query to start with (with XXX being the areas I think I need help with):

$querycomments = sprintf("SELECT * FROM comments WHERE " .
    "XXX = %s ORDER BY XXX DESC",
    GetSQLValueString(????????????, "text"));

Answer 1

Well, if you're looking for a way to just list the pages in order of most comments, I would group by page ID and then order by the count, something like:

select page_id, count(*)
from comments
group by page_id
order by 2 desc, 1 asc

Technically, you don't need the 1 asc but I like to ensure a specific order even within a descending comment count. So, if a lot of pages with an identical comment count appear, you can locate a specific page within that group easily. In other words, if page 7 had two comments and all other pages had just one, you'd get (7,1,2,3,4,5,6,8,9). Without the 1 asc, pages 1 through 6 and 8 through 9 could come back in any order, like (7,6,2,4,3,9,1,8,5) and it could even change between runs of the query.

For example, build a sample table:

> DROP TABLE COMMENTS;
> CREATE TABLE COMMENTS (PAGE_ID INTEGER,COMMENT VARCHAR(10));
> INSERT INTO COMMENTS VALUES (1,'1A');
> INSERT INTO COMMENTS VALUES (2,'2A');
> INSERT INTO COMMENTS VALUES (1,'1B');
> INSERT INTO COMMENTS VALUES (3,'3A');
> INSERT INTO COMMENTS VALUES (2,'2B');
> INSERT INTO COMMENTS VALUES (1,'1C');
> INSERT INTO COMMENTS VALUES (3,'3B');
> INSERT INTO COMMENTS VALUES (3,'3C');
> INSERT INTO COMMENTS VALUES (3,'3D');

Then show all the data:

> SELECT * FROM COMMENTS
  ORDER BY 1, 2;
    +---------+---------+
    | PAGE_ID | COMMENT |
    +---------+---------+
    |       1 | 1A      |
    |       1 | 1B      |
    |       1 | 1C      |
    |       2 | 2A      |
    |       2 | 2B      |
    |       3 | 3A      |
    |       3 | 3B      |
    |       3 | 3C      |
    |       3 | 3D      |
    +---------+---------+

Then run a select to group by descending comment count:

> SELECT PAGE_ID,COUNT(*) AS QUANT
  FROM COMMENTS
  GROUP BY PAGE_ID
  ORDER BY 2 DESC, 1 ASC;
    +---------+-------+
    | PAGE_ID | QUANT |
    +---------+-------+
    |       3 |     4 |
    |       1 |     3 |
    |       2 |     2 |
    +---------+-------+