Top Thread Starter

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.

[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
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 ).

[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:

PHP Code:

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