Board Stats Possible?

[bobdole2281]

I'm working on a query now. I'm thinking using   `smf_boards` and `smf_members` . I feel like I'd need one more though. Any ideas?

[butch2k]

I'm a bit lazy and most of all tired, so there is probably a more efficient way to do it but here is something to start with:

Code Select Expand

SELECT id_board, GROUP_CONCAT(distinct concat('member ',c.member,': ',c.cnt,' posts') order by c.cnt desc)
FROM smf_messages
JOIN ( SELECT id_board as board, id_member as member, count(id_msg) as cnt
  FROM smf_messages
  WHERE
    poster_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY))
  GROUP BY id_board, id_member
) c
ON c.board = id_board
GROUP BY id_board
ORDER BY id_board desc

of course the concat is for debug purpose, alter it for production use. This query (if i did not make any mistake...) should give you by boards the posters of the current month ordered by the number of posts.

[Arantor]

And it won't work inside SMF due to subselects being disabled...

[butch2k]

And it won't work inside SMF due to subselects being disabled...

$modSettings['disableQueryCheck'] ...
Yes i know it's dirty... 

The restriction on subselect is really painful when it comes to really complex queries. 

[bobdole2281]

Awesome. Thanks for the replies man, I really appreciate it.

Here is what I got:

Code Select Expand


$modSettings['disableQueryCheck'];

       
// All my login info here

$conM = mysqli_connect($hostnameM, $usernameM, $passwordM, $dbnameM) or DIE ('Unable to connect to database');

$SQL = "

SELECT id_board, GROUP_CONCAT(distinct concat('member ',c.member,': ',c.cnt,' posts') order by c.cnt desc)
FROM smf_messages
JOIN ( SELECT id_board as board, id_member as member, count(id_msg) as cnt
  FROM smf_messages
  WHERE
    poster_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY))
  GROUP BY id_board, id_member
) c
ON c.board = id_board
GROUP BY id_board
ORDER BY id_board desc";

/************************************************
Select all members and ID's
************************************************/
$resultM = mysqli_query($conM, $SQL);

while ($row = mysqli_fetch_array($resultM))
{
echo $row['id_member'];
echo $row['id_board'];
}

mysqli_close($conM);

What do I need to echo here, all I'm getting is: 2322164321

[margarett]

You are echoing the user and board ids with nothing between then. You need at least a line break or a space between them.
And now that you have the ids, what do you want to do with them?

[bobdole2281]

I get that I am echoing them with nothing in between them. The echo member returns blank.

The echo board is returning a board number. I'm just confused at what butch2k was intending me to echo. Do I even need the board ID's? Perhaps they tie in somewhere?

I'm looking for the member with the most posts in each group.

[butch2k]

It's the GROUP_CONCAT(distinct concat('member ',c.member,': ',c.cnt,' posts') order by c.cnt desc) which maters, it list by board the posters ordered by number of posts.

[bobdole2281]

Okay, wow. Thanks for the help ! What should I put to display that information?

The "by board the posters ordered by number of posts", is it in a separate Select statement?

[butch2k]

first of all run the query i posted then modify the concat for your needs so that you can use explode for instance to get everything in an array. The concat i posted is for readability.

[bobdole2281]

Thanks for the replies butch! Alright, I'll run that as soon as I can.

[bobdole2281]

This worked for me:

Code Select Expand

Select *
FROM  smf_messages
WHERE id_board = 3
And poster_time > 1378015261
And poster_name <> 'bobdole'
And poster_name <> 'hawkeye'
And poster_name <> 'DeanDoomIII'
And poster_name <> 'Captain Banhammer'

Those last posters are the 4 admins

1378015261 = September 1st in unixtimestamp

[Arantor]

So you collect every aspect of every message in the table. That query is going to absolutely hammer the database every time it's used. And I mean HAMMER the database.

[bobdole2281]

Good idea, I should just grab id_board, poster_time, and poster_name then correct?

Or can you think of any others I need?

[margarett]

I think that, if you just need the user with most posts in a certain board, you should try to get a query that extracts as little information as possible so that you have your results.

A "SELECT *" query will gather a LOT of information from the database, not to mention the HUGE amount of memory it will occupy... (of course, this depends on the size of the forums...)

[Arantor]

Well, since I never actually got an answer to what information you *actually* wanted, I have no idea if that's right.

Mind you, I still wouldn't do it that way, I'd be getting id_board, id_member and poster_time and then joining to smf_members to get their current member name (and only using poster_name if the member doesn't currently exist)

You see, you're clutching at straws on how to do this stuff and frankly you don't have much idea what you're doing, but you don't give anyone enough information to be able to actually help you make what you want. You need to spell it out in enough detail as to what you want - and not keep changing your mind about it. It's ALL relevant.

For example, you're getting the board id. Do you need the board id in the result set? Answer: almost certainly not. You're filtering on it, not getting it, you don't need to include it in the SELECT, only the WHERE.

Do you need all the post ids of all these posts? Answer: again, no, as I understand it you want the COUNT of posts, not the actual posts themselves.

Which means your total query is much closer to:

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, 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.

THIS is why I want to know everything I do - because you're asking the database the wrong question and ending up doing so much more work when the database can do it for you - and do it vastly more efficiently than you can - but only if you ask it the right questions. And to do that, you need to know what you're asking of it.

[bobdole2281]

Wow, thank you. That is some amazing code. I was getting the same result, but mine took about 20x longer and was far more complicated. I clearly have a lot to learn.

[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 =4
AND poster_time >1378015261
AND poster_name <>  'bobdole'
AND poster_name <>  'hawkeye'
AND poster_name <>  'DeanDoomIII'
AND poster_name <>  'Captain Banhammer'

This code seemed to be working, but now it's not calculating correctly. It's not showing the top poster every time, sometimes it's showing the second top poster and the "num_posts" seems to be off.

Does num_posts count edited posts as well by any chance?

[Arantor]

It doesn't count edited posts. It doesn't show the top poster, it just gets everyone who's posted in that board who isn't the administrator, because that's what you asked for. If it wasn't what you wanted and you wanted it listed by a certain order, you should have specified.

Mind you, I can see you haven't taken the advice I gave you even though it was firmly for your benefit. I think at this point I will firmly bow out.

[bobdole2281]

What advice would that be? If it's explain what information I actually want, I've literally done nothing but try to do that here. I'm just trying to get this to work man.