MySQl - update field by counting data in other table

Question

There are two tables. One is users info "users", one is comments info "comments".

I need to create new field "comments" in users table, that contains number of comments of that user. Table "comments" has "user" field with user's id of that comment.

What is optimal way to count number of comments by every user so far?

With php you should write script that selects every user and than count number of his comments and then update "comments" field. It is not hard for me, but boring.

Is it possible to do it without php, only in MySQL?

Answer 1

Why do you want to store it there anyway? Why not just show it combined query?

select users.name, count(comments.id) as comment_count
from users
join comments on users.id=comments.user_id
group by users.id

If you want to do it your way then include

update users set comment=comment+1 where id=$user_id

into the script where you store the comment.

And

update users set comment=comment-1 where id=$user_id

into the place where user can delete his comment. Otherwise your data might be out of sync when user adds new commnts and you haven't run the script yet.

Answer 2

UPDATE TABLE users SET CommentCount = (SELECT COUNT(*) FROM comments WHERE AuthorUserId = users.id)

Answer 3

Yes, it is possible. This is called table joining. You don't add another field to the users table, but to the resulting table.

SELECT users.*, count(comments.id) as num_comments 
FROM users,comments 
WHERE comments.cid=users.id 
GROUP BY users.id

Such a query is what relational databases were invented for. Do not revert it to the plain text file state. There is many reasons to make it that way.
http://en.wikipedia.org/wiki/Database_normalization <-- good text to read