Top Thread Starter

[Boofo]

Can someone please help me with a query that will pull the Top thread starter from a forum and the average threads that user has started in that forum? This is the only query I have left to do. ;)

[Xenon]

SELECT COUNT(*) as threads, postusername
FROM thread
WHERE forumid=xx
GROUP BY postuserid
ORDER BY threads DESC

[Boofo]

Thank you sir. ;) How would I get the user's average percent compared to the total threads in that forum?

Edit: I figured the percentage part out. Thank you, again, sir. ;)

How would I include the Announcements in the thread count? I have the Announcements as a thread hack (I think it is yours).

[Xenon]

announcements are in a different table, so it's not possible with one query.
they show up as multiple threads but they aren't real threads, therfore you have to run another query to get the announcements ;)

(at least i think union queries aren't possible with mysql?)

[Boofo]

Ok, but can I still somehow include the announcements thread in the total threads and top thread poster stats?

[filburt1]

Investigating.

(and /me smacks Stefan for using * in a query :p)

[filburt1]

Still investigating after I made a bad JOIN that made MySQL completely lock up my computer :D

[filburt1]

Okay, I have no idea. However MySQL 4.0.0+ supports UNION syntax, Stefan.

[Xenon]

yes, 4.0.0+ i thought of, but i don't think 3.x versions support em.

*?
hey i'm just lazy ;)

[Boofo]

Today at 01:51 PM filburt1 said this in Post #6 (https://vborg.vbsupport.ru/showthread.php?postid=396798#post396798)
Investigating.

(and /me smacks Stefan for using * in a query :p)

What would you use other than * in that query? It works here. I'm always open to new ideas. ;)

[Boofo]

My host is still using v3.23.54 anyway. ;)

[Xenon]

threadid would work :)

of course * works, it's just not needed to use a * if you can use the id field :)

[Boofo]

If you only have 2 thread starters, it always seems to pick the second thread starter. Is there a way to make it pick the first in line if there are more than one with the same amount of threads started?

[filburt1]

Today at 03:31 PM Boofo said this in Post #10 (https://vborg.vbsupport.ru/showthread.php?postid=396823#post396823)
What would you use other than * in that query? It works here. I'm always open to new ideas. ;)

It's considered bad practice to use a * in a query because you're fetching more data than you need to (so vB should die many, many times :D). :)

[Xenon]

@boofo: add after the order by another condition: , dateline ASC

[Boofo]

See? I learned something new here today. I didn't know you could do more than one ORDER BY in a query. ;) Here's what i am using now:

ORDER BY threads DESC, dateline DESC

When I used ASC, it still picked the second one. With DESC it picks the first one who made a thread if they are equal amounts. I think it should be that way, what do you think? ;)

Thanks again for the bailout and the lesson. ;)

[Xenon]

hmm dateline DESC would produce the last thread.

dateline descending -> so the thread with the higher dateline (later written) would be elected

[Boofo]

filburt, is it bad to use the * in this query then?

$yourposts = $DB_site->query_first("SELECT forum.forumid as forumid, forum.* as foruminfo, COUNT(post.postid) as yourposts FROM forum, thread, post WHERE forum.forumid=thread.forumid AND thread.threadid=post.threadid AND post.userid='".$bbuserinfo[userid]."' AND forum.forumid='$forumid' GROUP BY forumid ORDER BY yourposts DESC LIMIT 1");

[Boofo]

Today at 03:00 PM Xenon said this in Post #17 (https://vborg.vbsupport.ru/showthread.php?postid=396840#post396840)
hmm dateline DESC would produce the last thread.

dateline descending -> so the thread with the higher dateline (later written) would be elected

Would you also change this to ASC then?

ORDER BY threads DESC

[filburt1]

Today at 04:00 PM Boofo said this in Post #18 (https://vborg.vbsupport.ru/showthread.php?postid=396841#post396841)
filburt, is it bad to use the * in this query then?

$yourposts = $DB_site->query_first("SELECT forum.forumid as forumid, forum.* as foruminfo, COUNT(post.postid) as yourposts FROM forum, thread, post WHERE forum.forumid=thread.forumid AND thread.threadid=post.threadid AND post.userid='".$bbuserinfo[userid]."' AND forum.forumid='$forumid' GROUP BY forumid ORDER BY yourposts DESC LIMIT 1");

Very much so because it's getting all forum columns instead of the ones that are needed. It's great for expandability in the templates but otherwise is just a performance hit.

[Boofo]

The what would you use in place of the * in that query? Or is there a simpler and better way to do it?

[filburt1]

Just forum.whateveryouneed; for example: forum.title, forum.forumid.

[Boofo]

You mean like this?

$yourposts = $DB_site->query_first("SELECT forum.title, forum.forumid, COUNT(post.postid) as yourposts FROM forum, thread, post WHERE forum.forumid=thread.forumid AND thread.threadid=post.threadid AND post.userid='".$bbuserinfo[userid]."' AND forum.forumid='$forumid' GROUP BY forumid ORDER BY yourposts DESC LIMIT 1");

[filburt1]

Yes.

[Boofo]

So I don't need to declare the "forum.forumid as forumid" or the "forum.* as foruminfo"? And this will still give me the total post count for me in that forum (threads + replies)?