count the number of comments (php/mysql)

开发者_运维知识库i am using this code so i can count the number of comments for each article

SELECT *, COUNT(comment_id) as count
FROM article_comments
WHERE article_id =colname
GROUP BY article_id

this is what my comment table look like

http://i54.tinypic.com/2cdu3dk.png

i want to save these number in another table (the articles table.. each number next to it's article ) like this

http://i54.tinypic.com/2dgm82u.png

and when the user enter a comment..the number change automatically

someone help me with the code or if there is another way to do this

i know it's a long question but i have been trying to solve this for like..forever

thanx

You could set a TRIGGER that updates the comment count table every time a comment is added. Or you could simply add the UPDATE query right after the INSERT query in your comment page.

You probably do not need a lookup table. 1 article has many comments. Therefore, structure your comments table something like this (add an article field);

id | article | content
-------------------------
1  | 1       | Comment 1 for article 1.
2  | 1       | Comment 2 for article 1.
3  | 2       | Comment 3 for article 2. 

When displaying your article, list comments using the following query;

SELECT a.id, a.content FROM articles a WHERE a.article = :myArticleId

When creating a new comment:

INSERT INTO comments (article, content) VALUES (:currentArticleId, :content)
UPDATE article SET commentCount = commentCount + 1 WHERE article = :currentArticleId

The articles table will look something like this;

id | commentCount | content
------------------------------
1  | 0            | Article with 0 comments.
2  | 3            | Article with 3 comments.

This requires some work on your part, but it has more benefits than drawbacks.

Your proposed solution has 2 large drawbacks;

• COUNT() in SQL does not scale very well and can be slow, normally it can be avoided.
• The lookup table adds unnecessary complexity to your application.

Triggers should also always be avoided. They create "magic" conditions - your database can be changed without you knowing about it. Triggers are often more difficult to change than code too.

$query = mysql_query("SELECT * FROM article_comments WHERE article_id =".$youarticleId);

//the number of comments is :
$number_Of_Comments = mysql_num_rows($query);

//save it to another table

$query2 = mysql_query("UPDATE yourTable set numberOfComments =".$number_Of_Comments);

on saving comments, try to:

  update table_where_you_count_the_comments set number_of_comments = number_of_comments +1 where article_id = theID limit 1;

or look for mysql triggers.

you're asking the sql server to select everything and the count id at the same time, use one of them and give it a where close, and Bingo!