Affiliate Script with team help (Problem with recursion)

Navigation
3

I am writing a code on CodeIgniter for the release of free and non-profit cloud storage software.

However, in order to work, we will have an affiliate system, which allocates a certain amount of storage to the most advertisers, as well as to the members above.

The biggest difficulty is that the affiliate system is forked, that is, each level can only have a maximum of 5 users, when it is complete, look at the levels below to incorporate into 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 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 having for 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.)

Query in the database: 

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

    
asked by anonymous 20.05.2014 / 22:55

1 answer

1

You can try something with sub-select as: 

SELECT a.id FROM users a
WHERE (SELECT count(*) FROM users b WHERE upline_id = a.id) < 2
ORDER BY a.id
    
18.09.2014 / 23:59
Input type="number" with time mask How do I add a different object to a list every time I use the add?