Display full list of referrers and referrals

To see this in action go here.

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!

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

[Admin]

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.

[Admin]

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.

Thanks

[dwh]

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]

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:

<?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")."\");");
?>