top thread starters

[khaleejy]

hi

ive searched here for this script but i find this:
1) some script called top 10 thread starters but it doesnt order the users by their threads number.. it order by the posts number.. and that is not what i want
2) another one create a new table to get the right top 10 thread starters..

any one knows how can iget it without creating any table?? please

[Xenon]

i think this query should do it, so try to include it in your script :)

SELECT COUNT(threadid) AS threads, username LEFT JOIN user ON(user.userid=thread.postuserid) GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10

[khaleejy]

one more thing please
im not so good in php & mysql so could u help me with this:


$topts=$DB_site->query("SELECT COUNT(threadid) AS threads, username LEFT JOIN user ON(user.userid=thread.postuserid) GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10");
while($topts=$DB_site->fetch_array($topts)) {

$threadcount=$topts['threads'];
$topusername=$topts['username'];

}
eval("\$topts .= \"".gettemplate('forumhome_topts')."\";");
}
$DB_site->free_result($topts);
unset($topts);



would $threadcount return to the number of threads of the user?? and $topusername return to the username???

im not sure.. please tell if there is any mistake

[Xenon]

no, that wouldn't work, you use the same variablename for three different things...

this would work:
$topts=$DB_site->query("SELECT COUNT(threadid) AS threads, username LEFT JOIN user ON(user.userid=thread.postuserid) GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10");
$toptbits = "";
while($topt=$DB_site->fetch_array($topts)) {

$threadcount=$topt['threads'];
$topusername=$topt['username'];


eval("\$toptbits .= \"".gettemplate('forumhome_topts')."\";");
}
$DB_site->free_result($topts);

[Xenon]

sorry, little mistake ;)

use this:

$topts=$DB_site->query("SELECT COUNT(threadid) AS threads, username FROM thread LEFT JOIN user ON(user.userid=thread.postuserid) GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10");
$toptbits = "";
while($topt=$DB_site->fetch_array($topts)) {

$threadcount=$topt['threads'];
$topusername=$topt['username'];


eval("\$toptbits .= \"".gettemplate('forumhome_topts')."\";");
}
$DB_site->free_result($topts);

[Xenon]

look above :)
i've forgotten the "FROM thread" part *gg*

[khaleejy]

ive reached here:


// top thread starter
$topts=$DB_site->query("SELECT COUNT(threadid) AS threads, username FROM thread LEFT JOIN user ON(user.userid=thread.postuserid) GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10");
$toptsbits = "";
while($topts=$DB_site->fetch_array($topts)) {
$threadcount=$topts['threads'];
$topusername=$topts['username'];
eval("\$toptsbits .= \"".gettemplate('forumhome_topts')."\";");
}
$DB_site->free_result($topts);


and im getting this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/khaleejy/public_html/f/admin/db_mysql.php on line 154

notice: the line 154 has nothing to do with this script!!
line 152: unset($accessperm);
line 153:
line 154: // usergroup defaults
line 155: $usergroupdef['canview'] = $permissions['canview'];
line 156: $usergroupdef['canpostnew'] = $permissions['canpostnew'];

and im getting 1 username and his thread numebr only

[Xenon]

as i already told you above you can't use this:
while($topts=$DB_site->fetch_array($topts)) {
$threadcount=$topts['threads'];
$topusername=$topts['username'];

you HAVE to use this:while($topt=$DB_site->fetch_array($topts)) {
$threadcount=$topt['threads'];
$topusername=$topt['username'];

[khaleejy]

do uthink that i have to include: (( WHERE open='1' AND open<>10 )) in the query?

[khaleejy]

sorry.. i thought that you made a type mistake when u wrote "topt" i thought that u missed the "s".. ignore post #9 anyway its working GREAT right now.. thank you very much :)

[Xenon]

np :)

you're welcome :)

oh, for post #9
you can include a WHERE open<>10, it cannot hurt :)

[khaleejy]

maybe some one wants to know the final code:

* open index.php and find this:

if (!empty($iforumperms)) {
$iforumperms = 'AND forumid=' . implode(' OR forumid=', $iforumperms);
}


* replace it with:

if (!empty($iforumperms)) {
$iforumperms = 'AND forumid=' . implode(' OR forumid=', $iforumperms);
}

// top 10 thread starters
$topts=$DB_site->query("SELECT COUNT(threadid) AS threads, username, postuserid FROM thread LEFT JOIN user ON(user.userid=thread.postuserid) WHERE thread.open<>10 GROUP BY thread.postuserid ORDER BY threads DESC LIMIT 10");
$toptsbits = "";
while($topt=$DB_site->fetch_array($topts)) {
$threadcount=$topt['threads'];
$topusername=$topt['username'];
$userid=$topt['postuserid'];
eval("\$toptsbits .= \"".gettemplate('forumhome_topts')."\";");
}
$DB_site->free_result($topts);


* creat new template, called: forumhome_topts with this content:

<a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$userid">$topusername</a> : $threadcount<br>


* open the template 'Forum Home Page Templates/forumhome' and then write $toptsbits anywhere you want to show the top 10 thread starters.

just in case :cool:

thanks to Xenon

[Logik]

Thanks for that bit of code. That can give me something to study on. So i can learn PHP more.