how to show news amount the comment

[Linddha]

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

[diafol]

Could you explain further. Maybe with an example.

[Linddha]

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

[pritaeas]

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

[Linddha]

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.

[pritaeas]

Can you give an example of the output you want? Am not sure I understand what you want.

[Linddha]

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

[pritaeas]

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

[Linddha]

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

[pritaeas]

Is there a date column somewhere? Without it you can't query that.

[Linddha]

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

[pritaeas]

Add to the where:

AND DATEDIFF(NOW(), C.yourdatecolumn) < 7