Wedge

I wouldn't use the first option as you will end up executing two update statements for each user

The second option seems better, count all posts based on user ID into an array and then run an update statement when the value in the array is greater then the value in the user post count filed.

Or you could use a single query to update all users similar to the below query.

Code: [Select]

UPDATE {user_table}
SET {post_count}=(
SELECT COUNT({post_id})
FROM {post_table}
WHERE {user_id_field}={user_id}
)
WHERE {user_id_field}={user_id};

How so..? UPDATE {db_prefix}members SET posts = 0. That's it... A single query for ALL members.

Well, yes we could also store the original count, and only update if it's different (not higher...), that's for sure.

Or you could use a single query to update all users similar to the below query.

Code: [Select]

UPDATE {user_table}
SET {post_count}=(
SELECT COUNT({post_id})
FROM {post_table}
WHERE {user_id_field}={user_id}
)
WHERE {user_id_field}={user_id};

It'd be slightly more complex than this one, but generally speaking, I'd avoid using subselects because they're rarely faster than multiple well-optimized queries.