Order by comments
How can I list the top comments page on a site with PHP and mysql?
The database is configured 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 request him to order from the most commented pages?
Here is a simple query to start with (with XXX
being the areas I think I need help):
$querycomments = sprintf("SELECT * FROM comments WHERE " .
"XXX = %s ORDER BY XXX DESC",
GetSQLValueString(????????????, "text"));
a source to share
Okay, if you're looking for a way to just list the pages in the order of most comments, I would group by page id and then order an invoice, something like:
select page_id, count(*)
from comments
group by page_id
order by 2 desc, 1 asc
You don't technically need it 1 asc
, but I like to maintain a certain order even within a descending number of comments. This way, if many pages appear with the same number of comments, you can easily find a specific page in that group. In other words, if page 7 had two comments and all other pages had only one, you would get (7,1,2,3,4,5,6,8,9)
. Without 1 asc
pages 1 through 6 and 8 through 9 can be returned in any order, for example (7,6,2,4,3,9,1,8,5)
, and it can 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 the select to group command in descending order of the number of comments:
> 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 |
+---------+-------+
a source to share