Display top referrer on forum index

[Admin]

This was requested by PeterNRG and actually hacked by Freddie (but it's my thread :p).

1. In index.php find
// get newest member
$getnewestusers=$DB_site->query_first("SELECT userid,username FROM user WHERE userid=$numbersmembers[max]");
$newusername=$getnewestusers['username'];
$newuserid=$getnewestusers['userid'];
and above it add
// get top referrer
if ($usereferrer) {
$ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0 AND
user.userid NOT IN (1,5,7)
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 1");

eval("\$topreferrer = \"".gettemplate('topref')."\";");
}

2. Create a new template called topref. In this template, you can use $ref[referrals] (number of referrals), $ref[username] (for the top referrar's username) and $ref[userid] (for top referrar's user ID).
For example:
Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals

3. Add an index to field referrerid in table user.

4. Place $topreferrer wherever you want the topref template to be displayed.

That's it. Demo can be found here (http://forum.t-cove.com/index.php).

Good luck, and thank freddie! :)

[buro9]

i just applied this hack, as it's something i want on my board, but doing so caused the page to timeout and about 80 e-mails similiar to

Database error in vBulletin: Invalid SQL: SELECT count(*) AS count FROM user WHERE
referrerid = '350'
mysql error: Lost connection to MySQL server during query
mysql error number: 2013
Date: Sunday 12th of August 2001 01:11:17 PM
Script: /forum/index.php?s=
Referer:

to drop into my inbox.

could you advise whether this a problem with the query (inefficient or no indexes on the correct columns) or whether this is more a problem that you've tested this on a site with low qty of users and haven't been able to find out what would happen on a site with 1000+ members.

i would like the hack, but not at the expense of the homepage ;)

cheers

david k

[Admin]

Sorry, I forgot to mention that a new index is needed (I added it about a week ago, not related to this hack at all). Sorry. :(
I updated the first post. Also, please re-do step 2, as I forgot a = there. ;)

[SirSteve]

Can you elaborate on this?

3. Add an index to field referrerid in table user.

[Admin]

Take a look here (http://www.vbulletin.com/forum/showthread.php?s=&threadid=24946). :)

[Freddie Bingham]

You do not want to loop through every user and count their referrers! If you have 10,000 users, that would be 10,000 queries!

Why don't you work this into your hack instead:SELECT COUNT(*) AS count, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0
GROUP BY users.referrerid
ORDER BY count DESC
LIMIT 1

Referrals are done the way it is so that you can figure out what users a person has referred.

Also your code would appear to not work as you intended it to. You are trying to loop through all of the users and are using numbermembers but that will not work for people with > 999 users as it will have commas added to it. You should use $numbersmembers[user] if you really want to do that. Your code took 40 seconds to execute on my site. My suggestion took .03 seconds.

[Freddie Bingham]

// get top referrer
if ($usereferrer) {
$ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 1");

eval("\$topreferrer = \"".gettemplate('topref')."\";");
}

And template:

Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals. I wasn't trying to make you look bad. You can not loop through the entire user table querying each user. That would be a very bad thing (tm) to do. The timeout problems the above poster had were because of that.

[Admin]

Heh, I know you weren't. :)
But I'm new with this, so it's ok to mess up sometimes, eh? ;)

[PeterNRG]

Can we start over and put up the right code & explanations?

FireFly: I don't see #4 working on your board...
vB developers: get this hack optimized and included in the next vB version! :cool:

[Bane]

Is there a way to exclude a user from this list? For example, the admin?

[Freddie Bingham]

This will exclude users with userid 1, 5 and 7. Note the portion in bold that you would want to change.
// get top referrer
if ($usereferrer) {
$ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0 AND
user.userid NOT IN (1,5,7)
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 1");

eval("\$topreferrer = \"".gettemplate('topref')."\";");
}

And template:

Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals.

[Bane]

Thanks Freddie and Firefly :) These are great additions!

[SirSteve]

I agree with Peter... let's start over with fresh install instructions.

[Admin]

Done. Thanks a lot Freddie! :)

[Lucky]

Great work.

Thank you!

[Lucky]

Works with 2.2.2

Thanks again

[eg_92901]

I just tried installing and get nothing on my forum homepage.
I added it to the forumhome template and this shows up:
Top Referrer: with referrals

But no member or # of referrals. ???

I've checked myself several time and everything looks right.
I made the change to index.php
Added the topref template
Added $topreferrer to my forumhome template.

???

[Lucky]

Originally posted by freddie
// get top referrer
if ($usereferrer) {
$ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 1");

eval("\$topreferrer = \"".gettemplate('topref')."\";");
}

And template:

Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals. I wasn't trying to make you look bad. You can not loop through the entire user table querying each user. That would be a very bad thing (tm) to do. The timeout problems the above poster had were because of that.

You must do the above for it to work

[MrBojangle1]

OK i am very confused on what to do can someone put all the RIGHT steps on a txt file and send to me through PM or on AIM at SSJ4Gohan354

[Webmasta XT]

I applied the hack, and all I get is Nothing, nothing at all on forumhome, I did whatever freddie said..

[mfacer]

great hack.. got it to work perfectly!! (2.3.2)

maybe another good hack would be to show the top 10 referrers and their referral count?

I'm gonna try it, but if someone else has already done it, that'd be super!! ;)

www.yu-gi-oh-online.co.uk/forum/

[Riot-Boards]

Wow! I the code above actually made it work, fix your codes!

Nice Job!

[bigdaddy04]

does this have to be on index? what if I want to have this on a new template that I made?