Display full list of referrers and referrals

[Chen]

To see this in action go here (http://forum.t-cove.com/referrerlist.php).

Installation:
0. Download the attached zip file and extract it.
1. Create a new file named referrerlist.php and in it put the content of referrerlist.txt in it. Config $shownorefs.
2. Create a new template named reflist and put the content of reflist.txt in it.
3. Create a new template named reflistbit and put the content of reflistbit.txt in it.
4. Add an index to the field referredid in the table user.
5. Add a link to referrerlist.php?s=$session[sessionhash] anywhere you want.

Good luck, and feedback is welcomed! :D

[Cold Steel]

Great hack, thanks!

[badmeetsevil-]

Nice hack, but maybe you should have the names in descending order from the more referrals the have? I would rather have that rather then user ID.

[Cold Steel]

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!

Would that happen with this code as well?

[Chen]

Yeah I'm afraid so. :( But I don't know how to do this one in other way. It's not the same. :(

[dwh]

Same problem. I'm sure this code could be extremely simplified. It may take some thinking that I don't have the brain cells for now, but please don't take offense, I wouldn't install this one as is, although the concept is very good.

Edited to explain better. This script misses the whole point of a mysql database. SQL is optimized not to require looping through each member but rather selecting only those records having valid data. You need to learn these sql statements better before writing this kind of script.

Instead of counting the number of users you want to select all users where referrerid!="" select * from user where referrerid>0 or something like that. Look at Freddie's other code to see how he did it.

Try different things. Just go into phpMyAdmin and run the sql query and see what results you get. Fool around w/ your code, w/ Freddie's code. I'm sure you'll figure out the best way to do it.

[Chen]

Thanks for the tips. I'm only starting with these things, so I probably don't know everything. Or know very little. :)
So everyone, don't install this. It's cursed. :p

Thanks :)

[dwh]

Alright, instead of the code for referrerlist.php use this

Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



And replace the code for reflistbit with this

Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

and you should be all set. Enjoy.

[dwh]

Note that my code does differ a bit. I don't believe in releasing code like this with the option of displaying users that have no referrals. Why? Because once you have too many users it could be a HUGE page and cause you performance issues.

If you want to hack that back in you'd need to ask help from someone that knows the page navigation code because this code won't break it up into pages. And if you plan to list all the users you better have code to break it up into pages. Alternatively, you could jigger the memberlist page to show referrals. I don't think it'd be that hard.

BTW, I just realized there's a problem with this code, it'll add up all previous referrals, got to work on it some more.

[dwh]

OK, I fixed it. Use the reflistbit template I posted above and use this for the php file:



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[dwh]

We should also make the referred members in the right column clickable but I'm going to bed. Maybe when I wake up someone will post the answer ;)

[Chen]

Thanks dwh, I appreciate your help. :)

[unixman]

Here you go, clickable usernames in the last column - enjoy. Great hack ... :)



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[Bane]

I liked this hack so much I modified it a hair (as seen on http://www.talkloud.net/referrerlist.php ) and put an install script together for the templates.

The only real difference here is it adds the challenge to the top ( User XXX has the highest referrals with XX, can you do better? ) and has a fancy schmancy install file (ala-Kier). Should you want to give it a go feel free to DL the file here.

All credit goes to FireFly, and the others who modified his code :)

[dwh]

I haven't tried your code, but nice idea. One problem, on your site the top challenger you have listed is the second person w/ 2 referrals instead of the guy w/ 8 referrals.

[Bane]

There are three variables in the script now:

$max=?? - This number decides how many users to display
$userx=?? - Users to exclude from top referrer
$userl=?? - Users to exclude from top referrer list

On my board I have the admin [userid1] (me :) ) on the list but not on the top referrer for the board. It may look silly, but any admin worth his salt is going to have more referrals than his users I should think. If you dont like this you can set both userx and userl to 0 and it will list all.

Otherwise any userids you list will be excluded.

[george_proost]

Hi all,

My SQL is sooooooo bad.

Instead of the number of 'clean' referrals I would like to introduce the RIP. What is this RIP.

Well it is designed to give a workable indicator as to the effect of
a single member's referrals within a given timeframe.

How would it work ? Well ....
The RIP is calculated as follows :
-- you have a start date and an end date
-- one RIP point is added to the RIP period total for each directly referred new member.
-- add to this one half of the total RIP's for each directly referred new member.

The refererid becomes a sort of child and parent. And you probably have to work backwards somehow.

So no matter which time period you look at you get an accurate assessment of the members referal behaviour and referral effect.

I would like to award referal prizes based on something like this
instead of the 'one-on-one' hard referrals.

Can anyone help on this?

[dwh]

eh, are you trying to say that you want to pro-rate the referrals so that new members who referrred a lot of people in a short time get a better ranking than old timers who had more time to get referrals in? Like a referrals/day type deal but called RIP?

[george_proost]

Hi dwh,

Thanks for the reply... but no not really that way, and certainly not with that effect. Let me try expalin it in another way.

standard RIP logging - running totals in brackets

period1.
=====
Jack referes Jill and Simon -- Jack(2 RIP's)

period2.
======
Jill refers Pete -- Jill(1 RIP) and Jack(2,5 RIP's)

period3.
======
Pete referrs Paul --- Pete(1 RIP) Jill(1,5 RIP's) Jack(2,75 RIP's)

If the period to be measured is 'period 3' the actual standings would be :

Pete(1 RIP)
Jill(0,5)
Jack(0,75)

a member therefor ALWAYS for any given period WILL have a RIP value if a 'child' refereree at any level were to obtain a registration.

If Jack had refered 1 more member in period 3 in the current system of referals then would be a tie 1 RIP each. In the RIP system at the start of the period, depending on how large the referral tree is of a member and how deep there will ALWAYS be a RIP value ... albeit a very small one eg:

--1 direct referal in period
--0,5 referal once-revomed
--0,25 referal twice-removed
--0,125 ..
--0,0625 ..
--0,03125 and so on

This accurately measures the effect of refferals of a user over time within a given time period.

Hope this is clearer ..

[eva2000]

look the hack i installed the original version and works fine for my needs :)

[george_proost]

Yip, I agree eva2000.

I am looking for an exciting new alternative which is
'perpetually inclusive', I believe a member should get some credit
for even great-great-..-grand-referals.

I would like to offer prizes every 3 months.
At the start of a given period all RIP totals are 0.

If a referer joins from a related referred member ALL those in the
chain of referral benefit. The referral tree will eventually also be displayed for the period.

So even 1 referal starts it off and puts all members that have
residual RIP's on the prize list already.

[dwh]

I seee...hmm. This isn't too easy in my view and may add significant load on the server. I won't do it for you but I'll give you some direction as to how I'd do it if I was really set on it.

I don't believe you can do this with one sql statement based on the current table layout. But if you add some fields perhaps it can be done. Very complicated though. There may be some code somewhere to handle this. Look in the IPAddress code of vb. The problem is you need to do recursive searching. Or try I think it's php.net for some code.

Personally I think it's way more trouble than it's worth....if you can accept .5 instead of .5 then .25 etc it will be a little bit easier with a new field, but still a royal pain.

[futureal]

I have been trying to play around with this referrer stuff myself, and was wondering:

How difficult would it be to add a user field to the table that keeps track of a user's # of referrals, in addition to keeping track of "who referred who" like it does now?

I would like to be able to add a "Referrals" column to, for example, the Member Listing, without having to loop through the list for each user to see who referred who. Also, I would like to be able to sort the Member Listing based on # of referrals, which I have not been able to do so far (without an absolutely ludicrous amount of database queries). My goal in this regard is strictly numeric; the system already keeps track of who referred who, so all I am looking for a single numeric field that is incremented, say, when somebody registers.

I know you can add fields using the ALTER command though I know very little about it and don't want to screw anything up. It does seem that once a field like that was added, putting the rest together would be very easy.

Am I wrong, or would this be a relatively easy hack?

[george_proost]

I'm busy on a basic spec. It is not too difficult.
This is NOT a program but a way of specing a program.
Ideally it would become php and integrate passively with
vBulletin.

I am not a programmer so it could take yonks to do this.
A pro-programmer could probably do this in 3-4 hours.

It is NOT a hack as NO vBulletin code would be changed,
no new or altered tables nor new columns in tables.



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[Joshs]

Check this out: http://24.45.176.213:81/forums/referrerlist.php?s=

I have a question, what variable would I use to have it actually display the referred in the drop down menu? I can't figure it out... Right now I am trying the referrer value to make sure that it works and it does. I just cant seem to figure out what variable to use to display the correct information. Any ideas?

My referrer list bit:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



My referrer list:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[dwh]

Nice work. But the answer to your question is a bit involved and you'll have to play with it to get it right. You need to get the option statement out of the template and into the code. You need the option statement in a repeated loop or else you will only be able to get one username. I'm amazed that it isn't messing you up more.

[Joshs]

I think I have to make a new variable in the php file, but I dont know what to do. It is just the variable I need to figure out...

[dwh]

Nope that's not the case. You aren't looking for a variable, you need to move the html for the drop down box into the code.

Why? When the reflistbit is called it runs one line, 3 <TD>'s. But you need to loop through the last <TD> containing the list of referrers as many times as there are referrers. So your <option> html HAS to be in the code not in the template. You don't need a new variable name. The same one will do but you need the html inside the code.

[Joshs]

Okay I know that this code (below) will contain the $referrerlist I dont know what I have to incorporate into the php file, but I have no clue how to do it.



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[dwh]

I'm not using the topreferrer code and I looked at the code most of you are using but it was taking too long to adjust. So I'll post my code which is working great and hopefully you can figure it out.

BTW, looking at the code that was written for the top referrer, there was an unneeded template and an unneeded sql call.

The top referrer is the first userid that comes out in the loop. Simply set a variable the first time it is run then pull out all the sql code and extra template. So something like

if (!$topdone) {
$ref[userid]=$referred[userid];
$topdone=1;
}
placed right after the first fetch array should enable you to get rid of a bunch of code...

Anyways here's the code I'm using:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[Joshs]

What should my templates look like to make this work?

[Joshs]

bump

[hondastyle]

Great hack! Should generate some incentive for users to start doing some referring.

Thanks very much...It was an easy install and a very nice addition to my boards.

[dwh]

Well, this is how I have it though I don't have that stuff on top about the top referrer, though it should be easy enough to add in the code.

First towards the top of the reflist template I took your javascript


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



then you have your navbar etc then here's the table row



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



Then in the reflistbits template I've got:



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



hope that helps.

[dwh]

I think I just found a bug in vb. Notice that my vbcode for php is correct (i think)
yet when you use 3 php's the /php code doesn't seem to work....and it indents and puts funny colors.

[Joshs]

http://24.45.176.213:81/forums/referrerlist.php?s=

Pretty perfect... but there is an extra row at the end... any ideas as how to fix that?

[dwh]

I guess you modified my code to get the top referrer, post your templates and code changes..I do not have that problem.

[Joshs]

referrerlist.php:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



referrer_list:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



referrer_listbit:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

[dwh]

Do you have phpMyAdmin? Or have any other way to do an sql command? It may be an issue with the data in the database. If you can do an sql call run this query:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



I wouldn't be surprised if you get some weird result, but either way, post results here and we'll take it from there.

BTW, I really like your site design. You have a good eye for design.

[Joshs]

Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



Looked like it worked right...

[dwh]

Hey VB developers!! Don't miss this thread, there's definitely issues with the php vbcode!! The above "&n" actually reads "LIMIT 100", even when I edit it but no matter what i do it shows as &n
weird.

[dwh]

Originally posted by Joshs


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



Looked like it worked right...

note that the code I gave you got chopped, run it w/ the limit 100...

I thought an extra row may show up w/ some missing data....if not I'll look at the code again....somehow reflistbit is being called one time too many...

[Joshs]

I had to take out the &n because it said it was an invalid SQL statement... Could that be the problem?

[Joshs]

You have an error in your SQL syntax near '&n LIMIT 0, 30' at line 7

[dwh]

since the php vbcode isn't working, once again, here's the proper code to try:

"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 (0)
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 100"

[Joshs]

You have an error in your SQL syntax near 'LIMIT 0, 30' at line 7

[dwh]

You're using an old version of phpMyAdmin....just pull off the LIMIT 100 portion...then scan the results.

If there's more than 30, be sure to go to the next page and check that every line is ok.

[Joshs]

I am using the newest version of phpmyadmin... Everything that was pulled with that SQL looks alright... BTW whats your URL?

[dwh]

My boss won't let me post my url :(

OK, I have a question. How many rows were returned from the sql statement? It should have been 7.


Josh
Kckazdude
pug
DonnieD
cryzten
1gigkid
Andrew

[Joshs]

Yes, that is correct. Maybe its just a mistake in the php file or one of my templates...

[dwh]

Originally posted by Joshs
Yes, that is correct. Maybe its just a mistake in the php file or one of my templates...

I'm looking but this is very weird. Your reflistbit template is being called 8 times. If you view the source of your page you can see that and count it yourself. But the code will only loop thru as many times as your mysql statement comes back with...so how it can call the template 8 times when there's only 7 rows is just wacko.

[dwh]

found it!

Pull out

eval("\$reflistbits .= \"".gettemplate("referrer_listbit")."\";");
$referrerlist="";

from AFTER the TOP REFERRER section and you'll be fine...you WERE calling that listbit 8 times!

[Joshs]

Thanks so much!

[Steve_S]

Thanks everyone. I've got a JS pop up hacklette which lets your members send an email with the referral code automatically inserted.

See:

http://www.vbulletin.org/forum/showthread.php?postid=212838#post212838

[Gutspiller]

demo please. :(

[Floris]

I requested this in the request thing & bumped myself..

What I would like to see is some kind of hack where I can let users donate their gained referral points to other users.

Let say I as an admin have 5 points, and wich to give it to userX, it would be nice of I could select a referral and type in a referrers name. So he gets the points.

If this is not possible 1.2.3, then I would be happy with a script in the admin dir, which makes it more easy to do , then running manual mysql query's to replace referral points.

[wvvwnet]

It's a really great Job, Thanks
But, is there a way to insert the top 10 referals at the bottom of the main page of the forum,
please help

[pHAZE_1]

Originally posted by FireFly


1. Create a new file named referrerlist.php and in it put the content of referrerlist.txt in it. Config $shownorefs.

4. Add an index to the field referredid in the table user.

5. Add a link to referrerlist.php?s=$session[sessionhash] anywhere you want.


could someone please elaborate on the above instructions??? i understand the rest of them except those..

1. where does referrerlist.php go? whats Config $shownorefs??
4. ??
5. ??

[Webmasta XT]

Can we get new instructions for vb 2.2.6, cuz the top referrer on forumhome thing doesn't seem to work!!

[a43079]

nice

[Webdork]

Any working demos?

All the ones in this thread seem to have 404 errors.

[Visionray]

Um...In this thread I see 5 pages with big chunks of code in each post, and it seems as if the original post hasn't been updated.

What is the correct code to make this hack work? Is it in the original post?

[Alien]

I'd like to see this updated, with a final .txt of the least load version or whatever in one place. :) dwh's? It's not all in one place and the thread got nuts...

Anyone up to it?