Board Stats Possible?

[Arantor]

The part where I *very specifically* said NOT to use the list of names in the query.

AND m.id_member NOT IN (1, 2, 3, 4)

where the 1,2,3,4 are the user ids of the those you want to exclude which is MUCH MUCH MUCH faster than doing the comparisons you were doing. Which aren't even accurate anyway because if the user changes their display name, the new display name is what's stored - but their member id won't change.

And then, so you're getting a list of names and post counts. It isn't going to be returning those in any order. Did you want them in a certain order? Did you want only a certain number of them?

[bobdole2281]

Code Select Expand

SELECT m.id_member, COUNT(m.id_msg) AS num_posts, IFNULL(mem.real_name, m.poster_name) AS poster_name
FROM smf_messages AS m
LEFT JOIN smf_members AS mem ON (m.id_member = mem.id_member)
WHERE id_board = 3
And poster_time > 1378015261
AND m.id_member NOT IN (1, 7, 15, 48)

You were right. That way did work if I plugged in the right IDs. I didn't do it just to ignore you, I didn't know what it was or understand it at the time.

Here is what it displays:
id_member   num_posts   poster_name
98                   5                   DR. Strange

When I run it with my old statement it shows:

id_member   id_board   poster_time   poster_name
98   3   1378409542   DR. Strange
4   3   1378691872   Deadpool
4   3   1378691951   Deadpool
4   3   1378692121   Deadpool
4   3   1378692154   Deadpool

I need it to display these two variables:
Poster = Deadpool
NumberofPosts = 4

I'm not sure how to do that without an overly slow and complicated bunch of while statements. Is their a way to do it with count?

[bobdole2281]

SOLVED

Nevermind. I finally got it. Here is the sql if anyone is interested:

Code Select Expand

SELECT poster_name, COUNT(*) TotalCount
FROM smf_messages
INNER JOIN smf_members ON smf_messages.id_member = smf_members.id_member
WHERE id_board =22
AND poster_time >1378015261
AND smf_members.id_member NOT in (1,7,15,48)
GROUP BY poster_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

or

Code Select Expand

SELECT poster_name, COUNT(*) TotalCount
FROM smf_messages
INNER JOIN smf_members ON smf_messages.id_member = smf_members.id_member
WHERE id_board =1
AND poster_time >1378015261
AND poster_name <>  'bobdole'
AND poster_name <>  'hawkeye'
AND poster_name <>  'DeanDoomIII'
AND poster_name <>  'Captain Banhammer'
GROUP BY poster_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC