Need advise on a query

kpowning

I have a database table called users with the following fields:

username
password
userid
referrerid
referredby

I have a query at this time that shows members that were referredby another member. This works by getting using the field referrerid above. When a member referrers someone, their userid is inserted in the new members referrerid... How can I make a query that will only show members who were referred by someone and who has also referred a certain number of new members?? I hope this makes since. I added the referredby field just in case we can use it someway, somehow..

THANKS FOR ANY HELP IN ADVANCE 🙂

Frederick

You will need to add a field that will keep number of refferals ( refcount ).

<?php
$sql = "SELECT * FROM users WHERE referrerid > '0' AND refcount > '5' ORDER BY users";
$exc = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_array($exc)) {
	echo $row["username"]."<br>";
}
?>

kpowning

That is basically the same query that I'm using now just to get a members referral information:

$referrers = $DB_site->query("SELECT COUNT(*) AS referrals, user.username, user.userid, user.totalreferrals FROM " . TABLE_PREFIX . "user AS users INNER JOIN " . TABLE_PREFIX . "user AS user ON (users.referrerid = user.userid) 
                   WHERE users.referrerid > 0
                   GROUP BY users.referrerid 
                   ORDER BY referrals DESC");

HOW CAN I DO THIS?

Basically if user1 referred 2 new members, then user1's userid would be shown twice in the field referrerid for both new members. So therefore user1 has referred two members. So How can I make it so it will show user1's two new members plus these two new members referrals?? Any ideas.. PLEASE HELP..

kpowning

Can someone please help me on this??