count user posts

[turtle34]

Maybe I've been trying too hard and can't see the forest for the trees but here is my problem..

I have a members database with approx 500 people. Everytime someone enters a post, that members particular record is updated to show the actual accumulated number of posts they have made.

I want to be able to query ALL the members tables at once, find the total posts by each member, have the results sorted by most posts, with the actual numbers displayed..

I've tried soo many different things that I think I've confused myself by running in circles..

[Fumigator]

How about using GROUP BY?

SELECT user_id, count(*) as postcount
FROM table
GROUP BY user_id
ORDER BY postcount desc

[turtle34]

Never thought of the group function !! DOH !! Thanks a million Fum !!:D

[guelphdad]

You should not be counting something from your database and storing that information in a field. If you can calculate it from what you already have that is always the way to go. (i.e. don't store a field with total wins or losses for a sports table, just store the results of the games home/away/homescore/awayscore for instance and calculate the total wins/losses goals etc).

Welcome to Coding Forums by the way turtle34

[turtle34]

Thanks Guelph dad :)