MySQl - update field by counting data in other table
- by Qiao
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?
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?