PDA

View Full Version : Display full list of referrers and referrals


Chen
12 Aug 2001, 09:57
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
12 Aug 2001, 21:53
Great hack, thanks!

badmeetsevil-
12 Aug 2001, 22:11
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
13 Aug 2001, 02:48
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
13 Aug 2001, 04:27
Yeah I'm afraid so. :( But I don't know how to do this one in other way. It's not the same. :(

dwh
13 Aug 2001, 05:19
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
13 Aug 2001, 05:29
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
13 Aug 2001, 06:42
Alright, instead of the code for referrerlist.php use this <?php
error_reporting(7);
$templatesused='reflistbit,reflist';

require('./global.php');
$max=25; //This number decides how many users to display
$referrers = $DB_site->query("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 $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
$referreds = $DB_site->query("SELECT username FROM user WHERE referrerid = '$referrer[userid]'");
while ($referred = $DB_site->fetch_array($referreds)){
if ($referrerlist) {
$referrerlist.=", ";
}
$referrerlist .= $referred[username];
}
eval("\$reflistbits .= \"".gettemplate("reflistbit")."\";");
}
eval("dooutput(\"".gettemplate("reflist")."\");");
?>

And replace the code for reflistbit with this <tr align="center">
<td bgcolor="{firstaltcolor}"><smallfont><b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</a></b></smallfont></td>
<td bgcolor="{secondaltcolor}"><smallfont>$referrer[referrals]</smallfont></td>
<td bgcolor="{firstaltcolor}"><smallfont>$referrerlist</smallfont></td>
</tr> and you should be all set. Enjoy.

dwh
13 Aug 2001, 06:46
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
13 Aug 2001, 07:00
OK, I fixed it. Use the reflistbit template I posted above and use this for the php file:

<?php
error_reporting(7);
$templatesused='reflistbit,reflist';
require('./global.php');
$max=25; //This number decides how many users to display
$referrers = $DB_site->query("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 $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
$referreds = $DB_site->query("SELECT username FROM user WHERE referrerid = '$referrer[userid]'");
while ($referred = $DB_site->fetch_array($referreds)){
if ($referrerlist) {
$referrerlist.=", $referred[username] ";
} else {
$referrerlist = "$referred[username]";
}
}
eval("\$reflistbits .= \"".gettemplate("reflistbit")."\";");
$referrerlist="";
}
eval("dooutput(\"".gettemplate("reflist")."\");");
?>

dwh
13 Aug 2001, 09:00
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
13 Aug 2001, 09:56
Thanks dwh, I appreciate your help. :)

unixman
15 Aug 2001, 04:01
Here you go, clickable usernames in the last column - enjoy. Great hack ... :)


<?php
error_reporting(7);
$templatesused='reflistbit,reflist';
require('./global.php');
$max=25; //This number decides how many users to display
$referrers = $DB_site->query("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 $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
$referreds = $DB_site->query("SELECT username FROM user WHERE referrerid = '$referrer[userid]'");
while ($referred = $DB_site->fetch_array($referreds)){
$enc_username = urlencode($referred[username]);
if ($referrerlist) {
$referrerlist .= ", <A HREF=member.php?action=getinfo&username=$enc_username>$referred[username]</A>";
} else {
$referrerlist .= "<A HREF=member.php?action=getinfo&username=$enc_username>$referred[username]</A>";
}
}
eval("\$reflistbits .= \"".gettemplate("reflistbit")."\";");
$referrerlist="";
}
eval("dooutput(\"".gettemplate("reflist")."\");");
?>

Bane
15 Aug 2001, 09:37
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
15 Aug 2001, 18:00
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
15 Aug 2001, 18:16
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
23 Aug 2001, 16:54
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
23 Aug 2001, 20:13
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
24 Aug 2001, 08:13
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
24 Aug 2001, 08:21
look the hack i installed the original version and works fine for my needs :)

george_proost
24 Aug 2001, 08:43
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
24 Aug 2001, 09:44
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
31 Aug 2001, 07:07
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
31 Aug 2001, 07:26
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.



// userrips.php
//
// the intention of this program is to calculate the number
// of Referral Index Points or RIPs for short.
// =====================
//
// A RIP allows for the correct allocation of credit when even an n'th
// generation registration occurs. If the referrer is an ancestor of yours you
// still get credit for registration between date ranges
//
// This task needs to run periodically to keep track of things. (cronjob)
// it will probably be too greedy to run 'real-time'.
//
// the process described trickles RIP credits to all members
// for new member registrations within any given period of time

input parameters from_date, to_date

delete table userrips if it exists

create table userrips columns userid referrerid dateregistered rips

select userid, referrerid, dateregistered from user descending
// user table is no longer used in this program
// it will be queried again when the display needs to be shown
// the display can be a modified clone the current refertally program

insert userid, referrerid, dateregistered rips=0 into userrips

// table userrips will now be acted upon by two separate loops
// loop_1 just keeps the current user being analysed and is looped through
// processing only those users with a referrerid. This is a simple step_process loop.


foreach ( referrerid <> 0 AND dateregistered falls within date rangeparms ) //loop1
// set up start variables for each itteration of user
points = 1
luckyuserid = reffererid
// now trickle deminishing credit down the table
do while luckyuserid <> 0
udpate userrips rips with rips+points where userid = luckyuserid
select referrerid as luckyuserid from userrips userid = luckyuserid
points = points / 2
enddo
endfor

// end spec

Joshs
01 Sep 2001, 20:24
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:

<tr align="center">
<td bgcolor="#F1F1F1"><smallfont><b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</a></b></smallfont></td>
<td bgcolor="#F1F1F1"><smallfont>$referrer[referrals]</smallfont></td>
<form><td bgcolor="#F1F1F1" valign="middle" align="right" nowrap>
<SELECT NAME="newLoc" onChange="jumpPage(this.form.newLoc)" style="font-size: 7pt; background-color: #dfdfdf; font-family: Arial, Tahoma;" maxlength="20">
<OPTION VALUE="#">---------------------</OPTION>
<OPTION VALUE="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</OPTION></select></td></form>
</tr>


My referrer list:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD><TITLE>$bbtitle Top Referrer List</title>
$headinclude
</head>
<body>
$header
<!-- breadcrumb -->
<table border="0" width="100%" cellpadding="0" cellspacing="0"><tr>
<td width="100%"><img src="images/vb_bullet.gif" align="absmiddle"> <normalfont><b><a href="index.php?s=$session[sessionhash]">$bbtitle</a> &gt; Top Referrers</b></normalfont></TD></tr></table>
<!-- /breadcrumb -->

<!--Begin-->
<br>

<table cellpadding="0" cellspacing="0" border="0" bgcolor="#555576" width="100%" align="center"><tr><td>
<table cellpadding="4" cellspacing="1" border="0" width="100%">
<tr align="center">
<td bgcolor="#DFDFDF"><smallfont><a href="memberlist.php?s=$session[sessionhash]&action=search"><b>Advanced Search</b></a></smallfont></td>
<td bgcolor="#DFDFDF"><smallfont><a href="memberlist.php?s=$session[sessionhash]&what=topposters&perpage=$memberlisttopposters"><b>Top $memberlisttopposters Posters</b></a></smallfont></td>
<td bgcolor="#DFDFDF"><smallfont><a href="memberlist.php?s=$session[sessionhash]"><b>List Alphabetically</b></a></smallfont></td>
<td bgcolor="#DFDFDF"><smallfont><a href="memberlist.php?s=$session[sessionhash]&what=datejoined"><b>List by Date Joined</b></a></smallfont></td>
<!-- Begin Referrals -->
<td bgcolor="#F1F1F1"><smallfont><a href="referrerlist.php?s=$session[sessionhash]"><b>Referrals</b></a></smallfont></td>
<!-- End Referrals -->
<!-- Begin Forum Leaders -->
<td bgcolor="#DFDFDF"><smallfont><a href="showmods.php?s=$session[sessionhash]"><b>Forum Leaders</b></a></smallfont></td>
<!-- End Forum Leaders -->
</tr>
</table>
</td></tr></table>

<br>
<!--End-->

<!-- main -->
<!--
<table cellpadding="0" cellspacing="0" border="0" width="250" align="center"><tr>
<td><div align="center">$topreferrer</div></td>
</tr></table>
-->
<p align="center">$topreferrer</p>
<table cellpadding="0" cellspacing="0" border="0" bgcolor="#555576" width="250" align="center"><tr><td>
<table cellpadding="4" cellspacing="1" border="0" width="250">
<tr align="center">
<td bgcolor="#8080A6" align="center" width="100"><smallfont color="#EEEEFF"><b>Member</b></smallfont></td>
<td bgcolor="#8080A6" align="center" width="50"><smallfont color="#EEEEFF"><b>Referrals</b></smallfont></td>
<td bgcolor="#8080A6" align="center" width="100"><smallfont color="#EEEEFF"><b>Members Referred</b></smallfont></td>
</tr>
$reflistbits
</table>
</td></tr></table>

<p align="center">$forumjump</p>

<!-- /main -->
$footer
</body></html>

dwh
01 Sep 2001, 21:45
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
01 Sep 2001, 23:14
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
02 Sep 2001, 04:18
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
02 Sep 2001, 04:38
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.


<OPTION VALUE="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</OPTION>

dwh
02 Sep 2001, 05:06
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:
<?php
error_reporting(7);
$templatesused='reflistbit,reflist';
require('./global.php');
$max=25; //This number decides how many users to display
$referrers = $DB_site->query("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 $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
$referreds = $DB_site->query("SELECT username,userid FROM user WHERE referrerid = '$referrer[userid]'");
$referrerlist = "<SELECT NAME='newLoc' onChange='jumpPage(this.form.newLoc)' style='font-size: 7pt; background-color: #dfdfdf; font-family: Arial, Tahoma;' maxlength='20'><OPTION VALUE='#'>----------Select----------</OPTION>";
while ($referred = $DB_site->fetch_array($referreds)){
if ($referrerlist) {
$referrerlist.="<OPTION VALUE='member.php?s=$session[sessionhash]&action=getinfo&userid=$referred[userid]'>$referred[username]</OPTION>";
}
}
$referrerlist.="</select>";
eval("\$reflistbits .= \"".gettemplate("reflistbit")."\";");
$referrerlist="";
}
eval("dooutput(\"".gettemplate("reflist")."\");");
?>

Joshs
02 Sep 2001, 05:41
What should my templates look like to make this work?

Joshs
03 Sep 2001, 01:55
bump

hondastyle
03 Sep 2001, 03:41
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
03 Sep 2001, 06:06
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
<script>
<!--
function jumpPage(newLoc) {
newPage = newLoc.options[newLoc.selectedIndex].value
if (newPage != "") { window.location.href = newPage }
}
// -->
</script>

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

<tr align="center">
<td bgcolor="#8080A6" align="center"><smallfont color="#EEEEFF"><b>Member</b></smallfont></td>
<td bgcolor="#8080A6" align="center"><smallfont color="#EEEEFF"><b>Referrals</b></smallfont></td>
<td bgcolor="#8080A6" align="center"><smallfont color="#EEEEFF"><b>Members Referred</b></smallfont></td>
</tr>
$reflistbits

Then in the reflistbits template I've got:

<tr align="center">
<td bgcolor="#F1F1F1"><smallfont><b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</a></b></smallfont></td>
<td bgcolor="#DFDFDF"><smallfont>$referrer[referrals]</smallfont></td>
<form><td bgcolor="#F1F1F1"><smallfont>$referrerlist</smallfont></td></form>
</tr>

hope that helps.

dwh
03 Sep 2001, 06:10
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
03 Sep 2001, 16:03
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
03 Sep 2001, 19:39
I guess you modified my code to get the top referrer, post your templates and code changes..I do not have that problem.

Joshs
03 Sep 2001, 21:33
referrerlist.php:

<?php
error_reporting(7);
$templatesused='referrer_listbit,referrer_list,referrer_top';
require('./global.php');

// Top Referrer List Hack ############################################
// Hacked by: FireFly with help from dwh,unixman,Freddie,Bane
// Config #######################################################

$max=100; //This number decides how many users to display
$userx=0; //Users to exclude from top referrer Seperate with , (0 for none)
$userl=0; //Users to exclude from top referrer list Seperate with , (0 for none)

// Get Top List ###################################################
$referrers = $DB_site->query("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 ($userl)
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
$referreds = $DB_site->query("SELECT username,userid FROM user WHERE referrerid = '$referrer[userid]'");
$referrerlist = "<SELECT NAME='newLoc' onChange='jumpPage(this.form.newLoc)' style='font-size: 7pt; background-color: #dfdfdf; font-family: Arial, Tahoma;' maxlength='25'><OPTION VALUE='#'>------------Select------------</OPTION>";
while ($referred = $DB_site->fetch_array($referreds)){
if ($referrerlist) {
$referrerlist.="<OPTION VALUE='member.php?s=$session[sessionhash]&action=getinfo&userid=$referred[userid]'>$referred[username]</OPTION>";
}
}
$referrerlist.="</select>";
eval("\$reflistbits .= \"".gettemplate("referrer_listbit")."\";");
$referrerlist="";
}

// 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 ($userx)
GROUP BY users.referrerid
ORDER BY referrals DESC
LIMIT 1");

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

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

// ############################################################
makeforumjump();
eval("dooutput(\"".gettemplate("referrer_list")."\");");
?>


referrer_list:

{htmldoctype}
<HTML>
<HEAD><TITLE>$bbtitle Top Referrer List</title>
$headinclude
</head>
<body>
$header
<!-- breadcrumb -->
<table border="0" width="100%" cellpadding="0" cellspacing="0"><tr>
<td width="100%"><img src="{imagesfolder}/vb_bullet.gif" align="absmiddle"> <normalfont><b><a href="index.php?s=$session[sessionhash]">$bbtitle</a> &gt; Top Referrers</b></normalfont></TD></tr></table>
<!-- /breadcrumb -->

<!--Begin-->
<br>

<table cellpadding="{tableouterborderwidth}" cellspacing="0" border="0" bgcolor="{tablebordercolor}" {tableouterextra} width="{contenttablewidth}" align="center"><tr><td>
<table cellpadding="4" cellspacing="{tableinnerborderwidth}" border="0" {tableinnerextra} width="100%">
<tr align="center">
<td bgcolor="{secondaltcolor}"><smallfont><a href="memberlist.php?s=$session[sessionhash]&action=search"><b>Advanced Search</b></a></smallfont></td>
<td bgcolor="{secondaltcolor}"><smallfont><a href="memberlist.php?s=$session[sessionhash]&what=topposters&perpage=$memberlisttopposters"><b>Top $memberlisttopposters Posters</b></a></smallfont></td>
<td bgcolor="{secondaltcolor}"><smallfont><a href="memberlist.php?s=$session[sessionhash]"><b>List Alphabetically</b></a></smallfont></td>
<td bgcolor="{secondaltcolor}"><smallfont><a href="memberlist.php?s=$session[sessionhash]&what=datejoined"><b>List by Date Joined</b></a></smallfont></td>
<!-- Begin Referrals -->
<td bgcolor="{firstaltcolor}"><smallfont><a href="referrerlist.php?s=$session[sessionhash]"><b>Referrals</b></a></smallfont></td>
<!-- End Referrals -->
<!-- Begin Forum Leaders -->
<td bgcolor="{secondaltcolor}"><smallfont><a href="showmods.php?s=$session[sessionhash]"><b>Forum Leaders</b></a></smallfont></td>
<!-- End Forum Leaders -->
</tr>
</table>
</td></tr></table>

<br>
<!--End-->

<!-- main -->
<!--
<table cellpadding="{tableouterborderwidth}" cellspacing="0" border="0" {tableouterextra} width="250" align="center"><tr>
<td><div align="center">$topreferrer</div></td>
</tr></table>
-->
<p align="center">$topreferrer</p>
<table cellpadding="{tableouterborderwidth}" cellspacing="0" border="0" bgcolor="{tablebordercolor}" {tableouterextra} width="275" align="center"><tr><td>
<table cellpadding="4" cellspacing="{tableinnerborderwidth}" border="0" {tableinnerextra} width="275">
<tr align="center">
<td bgcolor="{tableheadbgcolor}" align="center" width="100"><smallfont color="{tableheadtextcolor}"><b>Member</b></smallfont></td>
<td bgcolor="{tableheadbgcolor}" align="center" width="50"><smallfont color="{tableheadtextcolor}"><b>Referrals</b></smallfont></td>
<td bgcolor="{tableheadbgcolor}" align="center" width="125"><smallfont color="{tableheadtextcolor}"><b>Members Referred</b></smallfont></td>
</tr>
$reflistbits
</table>
</td></tr></table>

<p align="center">$forumjump</p>

<!-- /main -->
$footer
</body></html>


referrer_listbit:

<tr align="center">
<td bgcolor="{firstaltcolor}"><smallfont><b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$referrer[userid]">$referrer[username]</a></b></smallfont></td>
<td bgcolor="{firstaltcolor}"><smallfont>$referrer[referrals]</smallfont></td>
<form><td bgcolor="{firstaltcolor}">$referrerlist</td></form>
</tr>

dwh
03 Sep 2001, 22:46
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:

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


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
03 Sep 2001, 22:50
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


Looked like it worked right...

dwh
03 Sep 2001, 22:51
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
03 Sep 2001, 22:53
Originally posted by Joshs

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


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
03 Sep 2001, 22:53
I had to take out the &n because it said it was an invalid SQL statement... Could that be the problem?

Joshs
03 Sep 2001, 22:54
You have an error in your SQL syntax near '&n LIMIT 0, 30' at line 7

dwh
03 Sep 2001, 22:58
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
03 Sep 2001, 23:00
You have an error in your SQL syntax near 'LIMIT 0, 30' at line 7

dwh
03 Sep 2001, 23:02
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
03 Sep 2001, 23:04
I am using the newest version of phpmyadmin... Everything that was pulled with that SQL looks alright... BTW whats your URL?

dwh
03 Sep 2001, 23:17
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
03 Sep 2001, 23:18
Yes, that is correct. Maybe its just a mistake in the php file or one of my templates...

dwh
03 Sep 2001, 23:20
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
03 Sep 2001, 23:24
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
03 Sep 2001, 23:47
Thanks so much!

Steve_S
17 Jan 2002, 17:48
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
28 Jan 2002, 21:45
demo please. :(

Floris
22 Mar 2002, 11:00
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
26 Mar 2002, 20:53
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
29 Apr 2002, 18:27
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
20 Jul 2002, 21:48
Can we get new instructions for vb 2.2.6, cuz the top referrer on forumhome thing doesn't seem to work!!

a43079
27 Sep 2002, 21:33
nice

Webdork
28 Sep 2002, 01:24
Any working demos?

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

Visionray
03 Dec 2002, 17:18
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
24 Jun 2003, 21:09
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?