Count number of messages per user
- by Pr0no
Consider the following tables:
users messages
----------------- -----------------------
user_id messages msg_id user_id content
----------------- -----------------------
1 0 1 1 foo
2 0 2 1 bar
3 0 3 1 foobar
4 3 baz
5 3 bar
I want to count the number of messages per user and insert the outcome into users.messages, like this:
users
-----------------
user_id messages
-----------------
1 3
2 0
3 2
I could use PHP to perform this operation, pseudo:
foreach ($user_id in users) {
$count = select count(msg_id) from messages where user_id = $user_id
update users set messages = $count
}
But this is probably very inefficient as compared to one query executed in MySQL directly:
UPDATE users SET messages = (
SELECT COUNT(msg_id) FROM messages
)
But I'm sure this is not a proper query. Therefore, any help would be appreciated :-)
