Need help with count

[kpowning]

I'm trying to make a script grab how many referrals a members has and then add them to how many they have had it the past. Basically its like a reset referrals system that I'm working on , but I have a row in my database named referrals where I need the total number of referrals to stay up-to-date for the live of the member.

Do I need to make a loop statement for this to work? Here is my code that I'm working with. Any help would be greatly appreciated.

$getold=$DB_site->query("SELECT COUNT(*) as ttl, user.username, user.userid FROM " . TABLE_PREFIX . "user AS users LEFT JOIN user AS user ON (users.referrerid = user.userid) 
                   WHERE user.userid > 0
                   GROUP BY users.referrerid 
                   ORDER BY ttl DESC");
$gettotalold=$DB_site->fetch_array($getold);
$referralcount=$DB_site->query("SELECT COUNT(*) FROM " . TABLE_PREFIX . "user
                       WHERE referrerid > 0");
$referralcounts = mysql_fetch_row($referralcount);
$lastreferrals=$DB_site->query("SELECT date FROM " . TABLE_PREFIX . "referrals");
$lastcount = mysql_fetch_row($lastreferrals);
if ($lastcount == ''){
$lastcount[0]="(No Reset Record Found!)";
}
if (($_REQUEST['do'] == 'resetreferrals') && ($referralcounts[0] > '0'))
{
echo"<table align=\"center\"><td>You have a total of: ($referralcounts[0]) referrals since your last reset that was on $lastcount[0]."; 
print_form_header('referralstats', 'resetnow');
	print_table_header($vbphrase['reset_referrals']);
	print_description_row($vbphrase['reset_referrals_notice']);
	print_submit_row($vbphrase['yes']);
echo"<br>";
print_form_header('referralstats','resetsingle');
print_table_header($vbphrase['reset_referrals_single']);
print_description_row($vbphrase['reset_referrals_notice2']);
print_submit_row($vbphrase['yes']);
}
//// The first query in this script/////
if (($_POST['do'] == 'resetsingle')&&($_POST['member'] != '')){
$DB_site->query("UPDATE " . TABLE_PREFIX . "user SET referrals = referrals+$gettotalold[ttl] WHERE userid = $gettotalold[userid]");
$test=$DB_site->query("SELECT * FROM user WHERE username = '".$_POST['member']."'");
$tester = mysql_fetch_array($test);
$DB_site->query("UPDATE " . TABLE_PREFIX . "user SET referrerid='0' WHERE referrerid = '$tester[userid]'");
define('CP_REDIRECT', 'referralstats.php?do=resetreferrals');
print_stop_message('yes_memberrefer_reset');
} else if (($_POST['do'] == 'resetsingle')&&($_POST['member'] == '')){
print_stop_message('no_referrals');
}
////The first query in this script////
if ($_POST['do'] == 'resetnow')
{
$DB_site->query("UPDATE " . TABLE_PREFIX . "user SET referrals = referrals+$gettotalold[ttl] WHERE userid = $gettotalold[userid]");
$DB_site->query("UPDATE " . TABLE_PREFIX . "user SET referrerid='0' WHERE referrerid > 0");
if ($lastcount[0] == '(No Reset Record Found!)') {
$DB_site->query("INSERT INTO " . TABLE_PREFIX . "referrals SET date='$todaysdate'");
define('CP_REDIRECT', 'referralstats.php?do=resetreferrals');
print_stop_message('yes_referrals_reset'); 
} else {
$DB_site->query("UPDATE " . TABLE_PREFIX . "referrals SET date='$todaysdate'");
define('CP_REDIRECT', 'referralstats.php?do=resetreferrals');
print_stop_message('yes_referrals_reset'); 
}
}
else if ($referralcounts[0] == '0'){
print_stop_message('no_referrals_found');
}
print_cp_footer();

I have marked with ////the first query in this script//// to show where I'm trying to keep the total working. Please help me here. Please

[Roger_Ramjet]

Really don't think I follow you. Forget all the html for now, just post the table schema and tell me which fields you want to get the data from and which ones you want to update.

[kpowning]

ok the table name is : user

It has the following fields in it:
userid
usergroupid
username
referrerid
password
referrals

The field referrerid is updated with the members userid who has referred him to the website. When I do a referral reset I need to select how many people were referred by each members userid and add that total to the already existing referrals.

Basically - referrals starts a (zero - the number) when a new members registers. After he refers a few people and the referals are reset, this field changes to the total number of people he referred. And always keeps adding up from there..

PLEASE HELP ME.. I HOPE THIS HELPS EXPLAIN IT. 🙂

[Roger_Ramjet]

Well, unless there is something you are not telling me, your logic is wrong. If you keep on adding to the last total then you will be double, treble, quadrupal, etc counting referals. At least that is how I understand you.

What you actually want is the total no of referals, full stop. So, unless you are deleting members, a straight count of each id will give you the current total.

SELECT refererid, count(userid)
FROM user
GROUP BY refererid

Will return the total count of referals for each refererid.

[kpowning]

ok - the field referrals in the database will always hold the total number of referrals each user has ever made.

the field referrerid will only hold one users userid if he was referred by that user. Once the referrals are reset, the users userid is removed from this field. But I'm wondering how I can always keep the total number of referrals accurate in the long run for the field referrals?? Does this make more since.. Thanks for the help....

[Roger_Ramjet]

Well, personally I can't see why you would remove the referer id. It is not hurting anything and is it may come in usefull one day. My basic rule is NEVER throw data away, you can't predict the future so you never know when it will come in handy. Only overwrite or delete it when it is a problem. Since the field will still be there you might as well keep the contents. I've got a database here that was 3 years old when they came and asked me, "Can we start tracking who changed the status?" To which I replied, "I already am. How do you want the report laid out?". Brownie point galore, he he.

Now as to your question about keeping it accurate. There is always the problem that records can be corrupted or deleted. My standard solution is to increment the count every time a new referal is added. With some dbs one would do this within a stored procedure or with a trigger, with mysql just run a second query when you add the new user.