rubysymal Posted July 16, 2015 Share Posted July 16, 2015 I am making commission based level software. I mean it is like distributorship. If ’A’ joins the system as distributor and joins three members (B,C,D) to the system then he should get extra 5% for every sales happening under him or under B, C, D. If suppose B also introduces three members( E, F, G) to the system under him then the 5% of every sales happening under B has to go to ‘B’ not to ‘A’. How can I write a coding for this in php? Here whenever a new member comes he has to check who is the one above him who is having the qualification and the 5% has to be given immediately to him automatically. Please help me in this problem.php.pdf Quote Link to comment Share on other sites More sharing options...
ignace Posted July 16, 2015 Share Posted July 16, 2015 It's really simple. Add a referred_by to your table and when IS NOT NULL then apply 5% to the ID. Quote Link to comment Share on other sites More sharing options...
rubysymal Posted July 16, 2015 Author Share Posted July 16, 2015 Thank you ignace, but what i am referring is only if a introduces 3 people then he should get 5%. if the person introduced by A ( eg: B) also introduces 3 people from under his geneology 5 % will go to B not to A. How to do it? Quote Link to comment Share on other sites More sharing options...
rubysymal Posted July 16, 2015 Author Share Posted July 16, 2015 Suppose I am in 100th level this 5% has to go to only the person above me who qualified with introducing 5%. So i have to search back till I get an ID who has introduced 3 direct referrals. Is there any loop to do it. Or else can u just explain the coding? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 16, 2015 Share Posted July 16, 2015 do it with a query mysql> SELECT * FROM test.refer; +----+------+------------+ | id | name | referer_id | +----+------+------------+ | 1 | A | NULL | | 2 | B | 1 | | 3 | C | 2 | | 4 | D | 3 | | 5 | E | 1 | | 6 | F | 2 | | 7 | G | 1 | | 8 | H | 3 | | 9 | I | 1 | | 10 | J | 3 | | 11 | K | 3 | | 12 | L | 4 | +----+------+------------+ SELECT a.id , a.name , GROUP_CONCAT(b.name ORDER BY b.name SEPARATOR ', ') as referrals , IF(COUNT(b.name) > 2, '5%', '') as commission FROM refer a INNER JOIN refer b ON b.referer_id = a.id GROUP BY a.id; +----+------+------------+------------+ | id | name | referrals | commission | +----+------+------------+------------+ | 1 | A | B, E, G, I | 5% | | 2 | B | C, F | | | 3 | C | D, H, J, K | 5% | | 4 | D | L | | +----+------+------------+------------+ 1 Quote Link to comment Share on other sites More sharing options...
rubysymal Posted July 16, 2015 Author Share Posted July 16, 2015 Thank You, But what I need is something else 1) Now on 16/07/2015 ID 1 (A) referred ID 2 (B), ID 3 © and ID 4 (D) 2) As he has referred 3 people he is eligible for a bonus 5% on any sales happening under him through him or through B, C AND D. 3) Now on 20/07/2015 ID 2 (B) also referred three people to business ID 5 (E), ID 6 (F) and ID 7 (G) 4) NOW ON WARDS what ever business happening under ID 2 (B) should fetch 5% only to ID 2 (B) and not to ID 1 (A). 5) ID 1 (A) will get 5% bonus from business happening under ID 3 © and ID 4 (D) till they also qualify by referring three persons each. I am having a table called register in MYSQL. In that there is a column called user_rank. if 3 referrals are there for an ID the user_rank column will have value "YES" When a new member joins the new Id has to see whether the up line or the person who sponsored him is having value YES in the user_rank column in register table. If not then it has to check whether the sponsor of his sponsor is having that , if not it has to check the sponsor's, sponsor's sponsor. This checking should go on till it finds an up line having a value YES inside the user_rank column . I am writing this coding in a php file. I need a php code for the same. Is there any loop which can go on checking rows up till the value is found. Please help me in this. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 16, 2015 Share Posted July 16, 2015 (edited) Why go to the trouble of maintaining that extra table with the YES flag when you can easily determine if a person has 3 referrals by querying the data you already have? Basic rule - do not store derived data. ids with 3 or more: SELECT referer_id FROM refer GROUP BY referer_id HAVING COUNT(*) > 2; Your search for sponsor's sponsor's sponsor will require a recursive function. Read the required data into an array for that otherwise it can get heavy on db server resources. Edited July 16, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted July 17, 2015 Share Posted July 17, 2015 A while ago someone asked about a tier system and made a function for it. http://forums.phpfreaks.com/topic/291591-wordpress-plugin-help/?do=findComment&comment=1492095 It may not be exactly what you need but can modify or get idea's from it. I see it being useful to know the values needed to insert into the database or some calculating after a query. Quote Link to comment Share on other sites More sharing options...
rubysymal Posted July 18, 2015 Author Share Posted July 18, 2015 Hi Mr.Barand, Thank you for your answer. I was also searching for a recursive function. But I am not an expert in php regarding a recursive function. So can you please show me how it look like in respect of the fields I am giving below 1) ID NO 2) Name 3) SPONSOR ID NO Suppose I am in 100th level I need to go on checking up till I find an ID in my up lines who has qualified by introducing 3 directs. I want to fInd the person who is nearer to me above me in the genealogy AND HAS TO GIVE 5%. Please help me in this. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 18, 2015 Share Posted July 18, 2015 try this <?php include("db_inc.php"); // define HOST, USERNAME etc $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT id , name , r.referer_id , IF(num.referer_id IS NULL, 'NO', 'YES') as flag FROM refer r LEFT JOIN ( SELECT referer_id FROM refer GROUP BY referer_id HAVING COUNT(*) > 2 ) num ON r.id = num.referer_id"; $people = array(); $res = $db->query($sql); while (list($id,$name,$rid,$flag) = $res->fetch_row()) { $people[$id] = array($name,$rid,$flag); } $newId = 15; $srch = hasSponsor3($newId, $people); // call to recursive function if ($srch !== false) { echo $srch; } else { echo "ID \"$newId\" does not exist"; } function hasSponsor3($id, &$people) /** * Search people tree for any with 3+ referrals * * @param $id - id of new person * @param $people - array of $people * @return id of sponsor, 0 if no sponsor found, false if error */ { if (!isset($people[$id]) ) return false; $referrer = $people[$id][1]; if (!$referrer) { return 0; // top of tree } if ($people[$referrer][2] == 'YES') { return $referrer; // found a referrer with 3 or more referrals } else return hasSponsor3($referrer, $people); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.