PDA

View Full Version : Display top referrer on forum index


Chen
12 Aug 2001, 05:36
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
12 Aug 2001, 09:15
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

Chen
12 Aug 2001, 09:29
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
12 Aug 2001, 12:47
Can you elaborate on this?

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

Chen
12 Aug 2001, 13:36
Take a look here (http://www.vbulletin.com/forum/showthread.php?s=&threadid=24946). :)

Freddie Bingham
12 Aug 2001, 15:02
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
12 Aug 2001, 15:59
// 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.

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

PeterNRG
12 Aug 2001, 17:04
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
12 Aug 2001, 20:55
Is there a way to exclude a user from this list? For example, the admin?

Freddie Bingham
12 Aug 2001, 20:57
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
12 Aug 2001, 21:06
Thanks Freddie and Firefly :) These are great additions!

SirSteve
13 Aug 2001, 00:43
I agree with Peter... let's start over with fresh install instructions.

Chen
13 Aug 2001, 05:40
Done. Thanks a lot Freddie! :)

Lucky
13 Jan 2002, 18:48
Great work.

Thank you!

Lucky
08 Feb 2002, 15:07
Works with 2.2.2

Thanks again

eg_92901
09 Feb 2002, 00:44
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
09 Feb 2002, 10:01
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
05 May 2002, 03:26
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
20 Jul 2002, 21:25
I applied the hack, and all I get is Nothing, nothing at all on forumhome, I did whatever freddie said..

mfacer
08 Sep 2003, 08:35
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
09 Apr 2004, 21:45
Wow! I the code above actually made it work, fix your codes!

Nice Job!

bigdaddy04
26 Apr 2004, 23:01
does this have to be on index? what if I want to have this on a new template that I made?