Display top referrer on forum index

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

1. In index.php find

PHP Code:

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

PHP Code:

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

Good luck, and thank freddie!

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