0

i want to show the favorite news base on calculation coment in comment table..
so ? what should i do?

3
Contributors
11
Replies
39
Views
3 Years
Discussion Span
Last Post by pritaeas
Featured Replies
  • 1

    Similar to the previous one: SELECT C.Id, C.Name, COUNT(*) AS CommentCount FROM Group G, Article A, Comment C WHERE A.Id_Group = G.Id_Group AND C.Id_News = A.Id GROUP BY C.Id ORDER BY CommentCount DESC Read More

0

i have 2 table article and comment

i want to show the best article 'top article' base on the calculation of comment
for example :
article :
id_news title content
1 | PHP | how we can learn PHP?
2 | HTML | how we can learn html?

Comment
id_comment id_news comment
1 | 1 | in school we can learn PHP
2 | 1 | in my web we can learn PHP
3 | 2 | HTML is easy in w3schools

Result :
top article 'desc'
1 PHP - 2 comment
2 HTML - 1 comment

0

Try:

SELECT A.Id, A.Title, COUNT(*) AS CommentCount
FROM Article A, Comment C
WHERE C.Id_News = A.Id
GROUP BY A.Id
ORDER BY CommentCount DESC
0

Great ! that's script run well..

bdw how if i want to show above value order by the group (table group).?
so i want to show top group,from amount coment in the article.

0

i have 3 table group, article and comment

i want to show the best article 'top article' base on the calculation of comment
for example :

group :
id_group name content
1 | Script
2 | Math

article :
id_news |id_group | title content
1 | 1 | PHP | how we can learn PHP?
2 | 2 | HTML | how we can learn html?
3 | 1 | PHPagain | how we can learn PHP again?

Comment
id_comment id_news comment
1 | 1 | in school we can learn PHP
2 | 1 | in my web we can learn PHP
3 | 2 | HTML is easy in w3schools
4 | 3 | in my web we can learn PHP again

Result :
top group 'desc'
1 Script - 2 comment
2 Math - 1 comment

1

Similar to the previous one:

SELECT C.Id, C.Name, COUNT(*) AS CommentCount
FROM Group G, Article A, Comment C
WHERE A.Id_Group = G.Id_Group
    AND C.Id_News = A.Id
GROUP BY C.Id
ORDER BY CommentCount DESC
Votes + Comments
great ! it works !
0

i've try your code.
but need a little changing

    SELECT C.Id, C.Name, COUNT(*) AS CommentCount
    FROM Group G, Article A, Comment C
    WHERE A.Id_Group = G.Id_Group
    AND C.Id_News = A.Id
    GROUP BY G.Id
    ORDER BY CommentCount DESC

bdw how if i want to show top group in a week?
or? 2 week ago?

i have try to use interval but not working

0

there is date colom in the article table and also comment table
use datetime

Edited by Linddha

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.