php – Affiliate script with team help (Problem with recursion)

Question:

I'm writing code in CodeIgniter, to promote a free non-profit cloud storage software.

However, in order to work, we're going to have an affiliate system, which allocates a certain amount of storage to whoever advertises the most, as well as to the members above.

The biggest difficulty is that the affiliate system is forced, that is, each level can only have a maximum of 5 users, when it is complete, look for the levels below to incorporate in the members below.

This is the structure of the users table:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| created      | datetime     | NO   |     | NULL    |                |
| join_ip      | varchar(30)  | NO   |     | NULL    |                |
| is_active    | tinyint(1)   | NO   |     | 0       |                |
| is_deleted   | tinyint(1)   | NO   |     | 0       |                |
| name         | varchar(100) | NO   |     | NULL    |                |
| email        | varchar(50)  | NO   |     | NULL    |                |
| country_id   | int(2)       | NO   |     | NULL    |                |
| sponsor_id   | int(20)      | NO   |     | NULL    |                |
| upline_id    | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

The function I have in the Model in PHP (CodeIgniter) is this:

public function getAvailableSponsor($sponsor = NULL)
{
    if ($sponsor == NULL) return array();
    $this->db->select('id, sponsor_name, sponsor_id, upline_id')
        -> from('users')
        -> where('upline_id', $sponsor);

    $query = $this->db->get();
    $result = $query->result();
    $total = count($result);

    if ($total < 2){
        return $sponsor;
    }else {
        foreach ($result as $row){
            return $this->getAvailableSponsor($row->id);
        }           
    } 

}

The sequence I'm getting as a result is this:

Sponsor ID 1 has 2 sponsored users FULL. 
Sponsor ID 2 has 2 sponsored users FULL. 
Sponsor ID 4 has 0 sponsored users Available :)

Next upline: 4 (AQUI deveria de ser 3, e nao 4.)

Database query:

 select id, sponsor_id, upline_id from users;

 +----+------------+-----------+
 | id | sponsor_id | upline_id |
 +----+------------+-----------+
 |  1 |          0 |         0 |
 |  2 |          1 |         1 |
 |  3 |          1 |         1 |
 |  4 |          1 |         2 |
 |  5 |          1 |         2 |
 +----+------------+-----------+

Any suggestion?

Answer:

You can try something with sub-select like:

SELECT a.id FROM users a
WHERE (SELECT count(*) FROM users b WHERE upline_id = a.id) < 2
ORDER BY a.id
Scroll to Top