'pyramid' like payments

[silent]

I am having a problem wrapping my brain around how to implement the following (maybe I'm just burnt out!) :

The company I am making this web site for allows users to refer
other people to the site, and get paid for it. The problem I am having, is how to write the logic for this - and I am wondering if my database design is 'correct'.

Here's the database structures :

table 'users'

id (user's id - primary)
name
pw
etc.. etc...
(extensive table, but the id is the only really necessary part of the paymet/referral tracking)

table 'refs' (user referrals)

id (pri key)
user_id (id of the user who is SIGNED UP)
parent (id of the user who referred this user)

table 'payments'

id
user_id
amount (amount of payment)
paid (enum Y/N - has this amount been paid yet?)
ref_id (referral that this payment pertains to)

I don't even know if ref_id is necessary on 'payments'.....

But here is where I am having the logic problem :
For example :

User1 has an account, and refers User2 (receives credit for signup - $0.01)

User2 signs up User3 and User4.
(User2 gets credit - $0.01 each user....
BUT User1 now needs to get paid for this as well because
User30->parent = User2->parent = User1

Any help is GREATLY appreciated. If anyone is willing to help me, but needs more clarification, let me know.

[th3void]

First, why do you have the parents in a seperate table? That seems like it would be just as easy to have that in the first table. And if the answer is blindingly obvious, you're not allowed to make fun of me, I'm sick.

Anyhow, I had to deal with this for forum posting myself (when you add it to one parent's total posts, add it to that parent's total posts, etc...). Basically, what I did was make a for loop that would go on forever until a certain condition was met that the for loop would never hit on it's own. Since every user you have in your pyramid has to originate from user #1, I just stop the loop whenever you hit user #1. Say a new user signed up and his signer was user #29...

$parent_user = 29;
for ($this_counter = 1; $this_counter > 0; $this_counter++) {
   do_payments($parent_user);
   $select_q = "SELECT parent FROM users WHERE id = '$parent_user'";
   $select_r = mysql_query($select_q, $connection);
   $new_parent = mysql_result($select_r, 0, 0);
   if ($new_parent == 1) {
      $this_counter = -1;
   }
   else {
      $parent_user = $new_parent;
   }
}

Obviously do_payments() is however you're going to pay the poor users. Also, that SQL query is based off of a structure I imagined, not hard to modify... I would think every user would just have a parent associated with him, with user #1 not having one.

Hope that helps, ask if you need any explanation.

[Ravenous]

Aside from that being a terribly ugly for loop (only because it's not letting the loop make the decision.. should use a different looping structure for this), that might work. Try putting all that in a while loop and then break(); when $parent_user = 1.

[barand]

Why are you both assuming that that only user 1 has no parent, there could be hudreds who signed up without being referred by someone else.

The loop should finish when the query to find a parent produces no results.

[barand]

Where you have this type of 'parent' table structure, the best solution is a recursive routine to seek out parents and make the payments. For example

function writePayment($user, $refid) {
         $isql = "INSERT INTO payments (user_id, amount, paid, ref_id)
             VALUES ($user, 0.01, 'N', $refid)";
         mysql_query ($isql);
}

function makePayments($user) {
         $sql = "select id,user_id,parent from refs where user_id=$user";
         $result = mysql_query($sql);
         if (list($refid,$userid,$parent) = mysql_fetch_row($result)) {
             writePayment($parent, $refid);
             makePayments($parent); // call itself for parent
         }
}

A single call

$userid = 3;
makePayments ($userid);

will make the payments to the parent of 3 (user2) and to the parent of 2 (user1)

hth

[daphreeek]

The top few scripts make you start at the bottom of the pyramid - you should start at the top.

The others only give one result - not really suitable if you want to know what to pay all the users (eg. run once a month)

Here is an easy way to do it:

<?
// This is the script to get the amount to pay each user


// include database info
include("../databaseinfo.php");


// make an array to store how many referrals each person made
$numRefs = array();


// fill the array with zero values
$result = mysql_query("SELECT max(user_id) AS max FROM refs",$db);
$tmp = mysql_fetch_array($result);

for ($x = 1; $x <= $tmp["max"]; $x++) {
   $numRefs[$x] = 0;
}


//  function to get number of referrals
function getNumRefs($user_id) {
   global $db;
   global $numRefs;
   if (!$numRefs[$user_id]) {

  $result = mysql_query("SELECT * FROM refs WHERE parent=$user_id",$db);

  while ($refs = mysql_fetch_array($result)) {
      $numRefs[$user_id] += getNumRefs($refs["user_id"]);
  }
  }

  return $numRefs[$user_id] + 1;
}


//  call function above
$result = mysql_query("SELECT * FROM refs WHERE parent=0",$db);

while ($refs = mysql_fetch_array($result)) {
   getNumRefs($refs["user_id"]);
}


//  Work out the amount to pay and display in a table
echo "<table border=1 cellpadding=5 cellspacing=0><tr><td>user_id</td><td>ammount due</td><td>ammount paid</td></tr>";
foreach($numRefs as $user_id => $refs ) 
{ 
echo "<tr><td>$user_id</td><td>" . $refs * .01 . "</td><td>0</td></tr>";
} 
echo "</table>";

?>

That bottom bit just displays the results at this stage so you need to change it to update a table 🙂

Change the users table to have 2 more fields (ammount_due, ammount_paid) both default values of 0. These will store the total ammount the user is entitled to and the ammount of that which has been paid. From these to, the ammount owing can be worked out.

At the end of the above script where I display the table, just do an update on the users.ammount_due field (eg: ammount_due += $0.01 * $refs)

When a payment is made, update users.ammount.paid to reflect the total ammount paid to the user.

Nice and easy 🙂

• Matt

[silent]

Wow, I owe all of you big thanks!
You've given me some direction, and I know
I can do this now. Thanks again!
(Now I'm off to go implement this.... my deadline for
minimal usable functionality for the site is Monday morning by 10:00am!!!!)

😃 😃 😃 😃 😃