-1

I have a table as below

CREATE TABLE member
(
    id VARCHAR(11) PRIMARY KEY NOT NULL,
    referral_id VARCHAR(10) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    address VARCHAR(100) NOT NULL,
    country VARCHAR(20) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    account_no VARCHAR(20) NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    bank_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    voucher_code VARCHAR(20) NOT NULL,
    vcode_activated VARCHAR(2) NOT NULL,
    current_level VARCHAR(2) NOT NULL,
    extra_downlines VARCHAR(2) NOT NULL,
    password VARCHAR(100) NOT NULL,
    status VARCHAR(2) NOT NULL,
    primary_downlines VARCHAR(2) NOT NULL,
    date_registered VARCHAR(12) NOT NULL,
    completed_level1 VARCHAR(2) NOT NULL,
    referral_bonus VARCHAR(10) DEFAULT '0',
    signup_earning VARCHAR(10) DEFAULT '0'
);
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100000', '0', 'Mose', 'Alfi', 'MKd', 'NA', '098', '987', 'Alfi', 'gtb', 'al@yahc.com', '1504895239', '0', '1', '0', '', '1', 'a', '', '', '2000', '2000');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100001', '100000', 'Ush', 'Alim', 'Mkd', 'AD', '0949490', '0987', 'Ush', 'FBN', 'us@alim.com', '', '0', '', '0', '', '0', 'ali', '0', '', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100002', '100000', 'Tork', 'Alik', 'Mkd', 'AU', '0987', '098', 'Tor', 'Diamond', 'torku@alik.com', '', '0', '', '0', '', '0', 'ali', '0', '', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100003', '0', 'Te', 'Aliu', 'Gboko', 'AD', '09809', '798', 'Aliu Ter', 'Stanbic', 'ali@er.com', '', '0', '0', '1', '0', '0', 'ali', '0', '2017-09-11', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100004', '100000', 'Alex', 'Alis', 'mkd', 'NI', '9890', '99', 'Alex', 'Eco', 'alis@yshso.com', '', '0', '0', '1', '0', '0', 'alex', '0', '2017-09-11', '0', '0');
INSERT INTO 
member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100005', '100000', 'Alx', 'Alki', 'mkd', 'NI', '9890', '99', 'Alx', 'Eco', 'alki@yshso.com', '', '0', '0', '1', '0', '0', 'alex', '0', '2017-09-11', '0', '0');

In the above member table, id is member id, referral_id is the id of the member that referred the member. A member is to refer 3 people who will become members and each of them is to refer 3 people. How do I get a member who has not referred up to 3 people? How do I also get a hierarchical display of a member and the people he has referred?

delboy1978uk
  • 12,118
  • 2
  • 21
  • 39
ali79
  • 23
  • 6
  • It'd be great if you could provide more readable description of the table. Like output of mysql DESCRIBE to show structure and a few lines of output of SELECT * FROM ... – Jarek.D Sep 12 '17 at 14:14
  • I've edited it to look nicer, we just need another editor to approve and you can read it – delboy1978uk Sep 12 '17 at 14:15

3 Answers3

0

For the first part of your question that should do. To roughly explain the query: we need UNION of a query that counts all people who referred more than zero but less than 3 people AND query that will list all those who haven't referred anyone.

(SELECT referring.id, 
        GROUP_CONCAT(
            CONCAT(referred.first_name, ' ', referred.surname)
       ) as Referred,
       CONCAT(referring.first_name, ' ', referring.surname) as Referral,
       COUNT(referred.id) as cnt
FROM member AS referred
LEFT JOIN member AS referring ON referring.id = referred.referral_id
GROUP BY referred.referral_id
HAVING cnt < 3)
UNION
(SELECT referring.id, 'nobody' as Referred,
       CONCAT(referring.first_name, ' ', referring.surname) as Referral,
       0 as cnt
 FROM member AS referring
 WHERE referring.id NOT IN (SELECT referral_id FROM member))

http://sqlfiddle.com/#!9/dc0b8/29

[UPDATE]

As for the second part of you question, in order to do a tree traversal in PHP you might modify the query so it lists the referred IDs as well:

    (SELECT referring.id, GROUP_CONCAT(CONCAT(referred.first_name, ' ', referred.surname)) as Referred,
       GROUP_CONCAT(referred.id) as Referred_ids,
       CONCAT(referring.first_name, ' ', referring.surname) as Referral,
       COUNT(referred.id) as cnt
FROM member AS referred
LEFT JOIN member AS referring ON referring.id = referred.referral_id
GROUP BY referred.referral_id
HAVING cnt < 3)
UNION
(SELECT referring.id, 'nobody' as Referred,
       'null' as Referred_ids,
           CONCAT(referring.first_name, ' ', referring.surname) as Referral,
           0 as cnt
     FROM member AS referring

 WHERE referring.id NOT IN (SELECT referral_id FROM member) )

http://sqlfiddle.com/#!9/dc0b8/32

The IDs of children might be a starting point to build a tree view of your hierarchy. But there is a big BUT.

Representing hierarchical data in MySQL is actually quite tricky subject. The way you represent your data is called adjacency list and is the simplest form, but the one that causes many issues with tree retrieval and management (adding removing data etc). According to many it is an antipattern.

In your specific case the retrieval problems might be even more acute, as your referral tree is going to be quite a deep one. Querying adjacency list representing a tree that does not have a fixed number of nesting levels is hard.

Have a look at this great article, to figure out problems that might lay in front of you, and possible solutions (alternative representation in db)

Jarek.D
  • 1,274
  • 1
  • 8
  • 18
  • why join the same table? – delboy1978uk Sep 12 '17 at 14:39
  • Because one column of the table references other column of the same table. – Jarek.D Sep 12 '17 at 14:41
  • I checked but it did not give the correct result. referral_id refers to parent id, while id refers to child id. A parent has children and each child can also give birth to children. This implies that a child has children is also a parent. The issue is how to get a child who does not have up to 3 children. I.e I will pick a parent and say give me his child that does not have up to 3 children. Another issue is, I will pick a parent id say '100000' and I will say give me his children and grand children and great grand children – ali79 Sep 12 '17 at 17:32
  • What exactly you mean by correct/incorrect results. You've asked two questions and all the answers here deal only with the first question - how to fetch the "parents" that have less than 3 direct descendants. The fact that you asked for direct descendant is implied from 'each of them is to refer three people'. So your first question doesn't deal with a deep hierarchy, the second does. Could you then clarify if you are satisfied with this part of the answer or not? The issues you mention sound to me like a new questions – Jarek.D Sep 13 '17 at 08:54
  • @Jarek.D I meant I tested the query but it did not give result as expected. I want For example Parent 1000 has child 1001, 1002, and 1003. Child 1001 gives birth to child 1004, 1005, Child 1002 give birth to 1006. Child 1003 give birth to 1007, 1008, 1009. From the example I have given here, only child 1003 has 3 children. I need query to get a child who does not have up to 3 children. from the example 1002 and 1003 have less than three children. And also I want to use php to display a parent and his children, and grand children in hierarchical order – ali79 Sep 13 '17 at 13:59
  • OK I've tested the query this time and you're right - the main problem was it didn't really list those who haven't referred anyone. See edited query. – Jarek.D Sep 13 '17 at 14:57
  • @Jarek.D I have tested and it worked very well. How do I add GROUP_CONCAT(referring.id) – ali79 Sep 13 '17 at 17:54
  • I've updated my post and there's a new fiddle with IDs added. There's a big BUT as well :) – Jarek.D Sep 14 '17 at 09:17
  • @Jarek.D That's great. It work well just that I don't get it using a particular referrer. How do I get a member of a referrer that referred less than 3 members. For example referral ID is 10000 and I want to get any of his downline that referred less than 3 members. And also I want pick referral Id 10000 and display all his children and grand children. I tried as below but it did not work. global $mysqli; $query = "SELECT id FROM member WHERE referral_id ='$referrer_id' AND COUNT(referral_id) < 3"; $result = $mysqli->query($query); $member = $result->fetch_assoc(); – ali79 Sep 14 '17 at 19:14
  • just use my SQL and do this: $ref_by_lt_3 = Array(); while ($row = $result->fetch_assoc()) { $ref_by_lt_3[] = explode(',', $row['Referred_ids']); }; or sth like that and you'll end up with array of arrays of members referred by less than three members. – Jarek.D Sep 14 '17 at 19:28
  • @Jarek.D That's great. I have a column signup_bonus in that table. What I wanted to achieve is that each a downline refer a member, the downline who referred the member gets 1000 as bonus, the member that referred that downline too get 1000, up to the first person that referred. I wrote a code that add the bonus but it only affects the referrer but not the parent of the referrer. $query = "UPDATE member SET signup_earning = signup_earning + 1000 WHERE id = '$referrer_id'"; $result = $mysqli->query($query); – ali79 Sep 14 '17 at 20:40
  • take your initial referrer_id and loop over SELECT referral_id FROM member WHERE id = '$referrer_id' reasigning referral_id to your referrer_id in each step until you reach the root. Store all collected IDs and then do one UPDATE statement with WHERE id IN ... clause – Jarek.D Sep 14 '17 at 22:14
  • Anyway, glad you're making a progress. Please consider accepting the answer you find most helpful here. Thanks – Jarek.D Sep 15 '17 at 09:03
  • @Jarek.D Thanks. I am making progress. Though it has not worked for me yet. I am still having a challenge doing it as you last explained. This is what I did. $ref = array(); $query = "SELECT referrer_id FROM member WHERE id='$referrer_id'"; $result = $mysqli->query($query); while ($member = $result->fetch_assoc()){ $ref = $member['referrer_id']; } – ali79 Sep 15 '17 at 09:33
  • You should run these queries inside while loop as opposed to looping over the results of single query. You just need a fetch_row there to get you parent ID, assign your parent to $referrer_id and loop these SELECTS in the while $referrer_id loop (it will stop when $referrer_id will be zero which is consistent with your data. – Jarek.D Sep 15 '17 at 10:18
0

One query to rule them all! Here you go amigo!

SELECT * FROM blah WHERE id =
(SELECT rid
FROM blah 
GROUP BY rid 
HAVING COUNT(DISTINCT id) < 3);

http://sqlfiddle.com/#!9/7046f2/17

delboy1978uk
  • 12,118
  • 2
  • 21
  • 39
0

This expression returns children (including grand children) of the main parent (id 100000) which have less than 3 own children.

SELECT `id`, `referral_id` AS `parent_id`, `children`
FROM (SELECT `id`, `referral_id`, (SELECT COUNT(`id`) FROM `member` AS `r` WHERE r.`referral_id` = m.`id`) AS `children` FROM `member` AS `m` ORDER BY `referral_id`, `id`) AS `members`,
     (SELECT @pv := '100000') AS `init`
WHERE FIND_IN_SET(`referral_id`, @pv) > 0 AND @pv := CONCAT(@pv, ',', `id`)
HAVING `children` < 3

http://sqlfiddle.com/#!9/885878/4

If a member has 3 or more children then its grand children will be excluded from the result too.

SELECT `id`, `referral_id` AS `parent_id`, `children`
FROM (SELECT `id`, `referral_id`, (SELECT COUNT(`id`) FROM `member` AS `r` WHERE r.`referral_id` = m.`id`) AS `children` FROM `member` AS `m` HAVING `children` < 3 ORDER BY `referral_id`, `id`) AS `members`,
     (SELECT @pv := '100000') AS `init`
WHERE FIND_IN_SET(`referral_id`, @pv) > 0 AND @pv := CONCAT(@pv, ',', `id`)

http://sqlfiddle.com/#!9/885878/5

In this answer you can find an additional info on recursive queries. https://stackoverflow.com/a/33737203/4020014

Talgat
  • 314
  • 1
  • 2
  • 5
  • I checked but it did not give the correct result. referral_id refers to parent id, while id refers to child id. A parent has children and each child can also give birth to children. This implies that a child has children is also a parent. The issue is how to get a child who does not have up to 3 children. I.e I will pick a parent and say give me his child that does not have up to 3 children. Another issue is, I will pick a parent id say '100000' and I will say give me his children and grand children and great grand children. – ali79 Sep 12 '17 at 17:29
  • @ali79 Could you show an example of what you want to get? – Talgat Sep 12 '17 at 17:57
  • For example Parent 1000 has child 1001, 1002, and 1003. Child 1001 gives birth to child 1004, 1005, Child 1002 give birth to 1006. Child 1003 give birth to 1007, 1008, 1009. Here only child 1003 has 3 children. I need query to get a child who does not have up to 3 children. i.e 1002 and 1003. Using php I want to also display a parent and his children, and grand children in hierarchical order – ali79 Sep 12 '17 at 21:28