Home > Code Snippets, mysql > Binary tree in MySQL for MLM

Binary tree in MySQL for MLM

October 3rd, 2009 Leave a comment Go to comments

Recently for a student, I was asked to explain the design considerations of a Binary Tree which was to be used in an MLM solution. About 10 years back it was a nightmare, and in my career, I was lucky to get that privilage for more than a dozen times with varying schemes and structures. But now with MySQL having procedures, and functions, the tree design and related functions were a breze. Reproducing it here for future reference. This is in no way a complete solution, but just bits and pieces which may even be discarded as crap.

CREATE TABLE  `binTree` (
  `nodeid` int(10) unsigned NOT NULL auto_increment,
  `lnode` int(10) unsigned NOT NULL default '0',
  `rnode` int(10) unsigned NOT NULL default '0',
  `pnode` int(10) unsigned NOT NULL default '0',
  `pside` enum('l','r') NOT NULL default 'l',
  `tLevel` int(10) unsigned NOT NULL default '1',
  PRIMARY KEY  (`nodeid`),
  KEY `parent` (`pnode`),
  KEY `treelevel` (`tLevel`),
  KEY `lside` (`lnode`),
  KEY `rside` (`rnode`)
) ENGINE=MyISAM;

The above is the basic structure of the tree table, and some complementary functions and procedures are accompanied to make the usage simple, otherwise would be a herculian task for the developer to do the same.

CREATE PROCEDURE  `addTreeNode`(iParent int, cSide char(1))
BEGIN

  declare iCheck int;
  declare iLevel int;  
  declare iNewNode int;

  select if(@silentmode is null, 0, @silentmode) into @silentmode; 

  # need to do some validations, since this is add.. and not change
  # check that parent side is vaccant

  if cSide = 'l' then
     select lnode into iCheck from binTree where nodeid = iParent;
  end if;
  if cSide = 'r' then
     select rnode into iCheck from binTree where nodeid = iParent;
  end if;
  if iCheck = 0 then
     # okay we can start applying
     select tLevel into iLevel from binTree where nodeid = iParent;
     insert into binTree ( `pnode`, `pside`, `tLevel`) values (iParent, cSide, iLevel + 1);
     select last_insert_id() into iNewNode; 
     if cSide = 'l' then
        update binTree set lnode = iNewNode where nodeid = iParent; 
     end if;
     if cSide = 'r' then
        update binTree set rnode = iNewNode where nodeid = iParent; 
     end if;
  else
     set iNewNode = 0;
  end if;

  if @silentmode = 0 then
     select iNewNode as res;
  end if;
END

The addTreeNode procedure is called to add a new node into the system, if need to use this, one should have a login, and profile table different, and then invoke the procedure with parentid and l/r for left/right, which will return the userid, which should be used as primary key to save login info as well as contact info.

CREATE PROCEDURE `getTreeFrom`(iNode int)
BEGIN

   declare iMaxLevel int;
   declare iLevel int;
   declare iCheck int;
 
   select tLevel + 1 into iLevel from binTree where nodeid = iNode;
   select max(tLevel) into iMaxLevel from binTree;

   # cant play without temporary tables;
   create temporary table tNodes (itNode int unsigned );
   create temporary table uNodes (itNode int unsigned );

   insert into tNodes value(iNode);
   loop1: LOOP
     if iLevel > iMaxLevel then
        LEAVE loop1;
     end if;     

      insert into uNodes select nodeid from binTree where pnode in (select itNode from tNodes) and tLevel = iLevel;   
      select count(*) into iCheck from uNodes;
      if iCheck = 0 then
         LEAVE loop1;
      end if;
      insert into tNodes select itNode from uNodes;
      truncate table uNodes;  
     set iLevel = iLevel + 1;
   END LOOP;

   select bt.* from binTree bt, tNodes tn where tn.itNode = bt.nodeid order by bt.tLevel, bt.nodeid; 

   drop table uNodes;
   drop table tNodes;

END

This procedure would be a bit too much on systems when the tree called from is one of the top range.. still we cannot let the system go without one like this and hence any tree starting from a node can be selected using getTreeFrom.

CREATE PROCEDURE `pruneNode`(iNode int, iParent int, sSide char(1))
BEGIN
  declare iCheck int;
  declare iNewLevel int;
  declare oParent int;
  declare oSide char(1);
  #request is to pluck iNode from existing parent and attach to new parent in said side
  #side should not be empty.. check that first
  if (sSide = 'l') then
    select lnode into iCheck from binTree where nodeid = iParent;
    if iCheck = 0 then
       update binTree set lnode = iNode where nodeid = iParent;
    end if; 
  end if;
  if (sSide = 'r') then
    select rnode into iCheck from binTree where nodeid = iParent;
    if iCheck = 0 then
       update binTree set rnode = iNode where nodeid = iParent;
    end if; 
  end if;
  
  if (iCheck > 0) then
      select 0 as res;
  else
      select pnode into oParent from binTree where nodeid = iNode;
      select pside into oSide from binTree where nodeid = iNode; 
      update binTree set pnode=iParent, pside=sSide where nodeid = iNode;
      if oSide = 'l' then
         update binTree set lnode = 0 where nodeid = oParent;
      end if;
      if oSide = 'r' then
         update binTree set rnode = 0 where nodeid = oParent;
      end if;
      select tLevel into iNewLevel from binTree where nodeid = iNode;
      CALL stabilizeLevels(iNewLevel);
  end if;

END

This one function was just out of the nostalgy, since this was demanded by one of my clients, and we just said it was not possible thinking of all the recursive calls we had to implement using php, but now just a wimph.. The parameters are node to be pruned, new parent, and side of new parent. Resulting result set will contain single column single row, either ‘0’ ( means the new parent’s side was not empty ) or ‘1’ ( means the operation was successful ). This uses a complementary procedure for which the listing is reproduced below.

CREATE PROCEDURE `stabilizeLevels`(iLevel int)
BEGIN

   declare iCheck int;
   declare iMaxLevel int;
   declare iNCount int;
   declare sNodeList text;
   if iLevel = 1 then
      select 0 as res;
   else
   #starting from iLevel.. traverse to iMaxLevel and make sure all nodes are correct.. 
   select max(tLevel) into iMaxLevel from binTree;
 
   loop1: LOOP

     if (iLevel > iMaxLevel) then
        LEAVE loop1;
     end if;

     create TEMPORARY table mylist (iNode int);
     insert into mylist select nodeid from binTree where tLevel = iLevel;
     select sum(checkLevel(iNode) * 1) into iCheck from mylist;
     drop table mylist;

     if (iCheck = 0) then
	LEAVE loop1;
     end if;

     set iLevel = iLevel + 1;
   END LOOP;

      select 1 as res;
   end if;

END

And another function which was just out of sheer curiosity..

CREATE FUNCTION `checkLevel`(iNode int) RETURNS enum('0','1') CHARSET latin1
BEGIN
  declare iParent int;
  declare iMyLevel int;
  declare iCheck int;
  declare iMaxLevel int;

  if (iNode = 0) then
     return '0';
  end if;
  #get parent level and make sure the difference is 1 if not.. go deep and update the levels

  select count(*) into iCheck from binTree where nodeid = iNode;
  if iCheck = 0 then
     return '0';
  end if;
 
  select tLevel into iMyLevel from binTree where nodeid = iNode;
  select pnode into iParent from binTree where nodeid = iNode;

  select iMyLevel - tLevel into iCheck from binTree where nodeid = iParent;

  if iCheck <> 1 then
    select tLevel + 1 into iMyLevel from binTree where nodeid = iParent;
    update binTree set tLevel = iMyLevel where nodeid = iNode;
    return '1';
  else
    return '0';
  end if;

END

The above system is not benchmarked, neither tested in realtime. But if it becomes of use to any one of you, please add a comment to the comments box here.

Sunday, Oct 4.

Just added a couple of procedures more to test and do some benchmarks. Adding those procedures to this post, for future reference.

CREATE PROCEDURE  `initTree`()
BEGIN
   truncate table binTree;
   insert into binTree values (1,0,0,0,'l',1);
   select * from binTree;
END

Just got tired of truncating the table, inserting the initial row etc..

CREATE PROCEDURE  `gFillNodes`(iParent int, iCount int, bRandPlace binary(1))
BEGIN
  declare sLeg char(1);
  declare iTest int;
  declare iLevel int;
  #start the loop
  set @silentmode = 1;
  loop1: LOOP
    if iCount = 0 then
	LEAVE loop1;
    end if; 

    if bRandPlace = 1 then
      select floor(rand() * 100) % 2 into iTest;
      if iTest = 0 then
         set sLeg = 'r';
         select min(nodeid) into iParent from binTree where nodeid >= iParent and rnode = 0;
      else
         set sLeg = 'l';
         select min(nodeid) into iParent from binTree where nodeid >= iParent and lnode = 0;
      end if;
    else
      select min(tLevel) into iLevel from binTree where nodeid >= iParent and (lnode = 0 or rnode = 0);
      select count(*) into iTest from binTree where lnode = 0 and tLevel = iLevel;
      if iTest = 0 then
         select min(nodeid) into iParent from binTree where rnode = 0 and tLevel = iLevel;
         set sLeg = 'r';
      else
         select min(nodeid) into iParent from binTree where lnode = 0 and tLevel = iLevel;
         set sLeg = 'l';
      end if;
    end if;

    CALL addTreeNode(iParent, sLeg);
    set iCount = iCount - 1;
  END LOOP;

END

Random as well as sequential fill of the tree.. one can see the working in the screen shot of the console..
proc_work

Addition

Few days back some body was asking me, how I would go about to finding the parent node of parent node of given node, well I did not have to think much, than kick in the following listing as a mysql function. Since mysql does not support arrays (afaik), well that is not a big problem, we could split the return value in any of the programming language.

CREATE FUNCTION `getAncestors`(iNode int, iSteps int) RETURNS text
BEGIN
 declare sAncestors text;

 if (iSteps = 0) then
    select count(*) into iSteps from binTree where nodeId <= iNode;
 end if;

 loop1: LOOP
   if ( iSteps <= 0 ) then
       LEAVE loop1;
   end if;

   select pnode into iNode from binTree where nodeid = iNode;

   if (iNode = 0) then
       LEAVE loop1;
   end if;

   if(length(sAncestors) > 0) then
     set sAncestors = concat(sAncestors,',',iNode);
   else
     set sAncestors = concat(iNode,'');
   end if;
   
   set iSteps = iSteps - 1;

 END LOOP; 
  
 RETURN sAncestors;
 
END

  1. mike
    November 11th, 2009 at 11:12 | #1

    I’m a student and an amateur web designer and needs to create a binary mlm system using php mysql. i really need it badly to study. Can you send me a sample 1 with database configuration and a simple add-user-to-tree system?

    here’s my email icodeme@gmail.com thank you so much!

  2. November 13th, 2009 at 07:22 | #2

    I am commercially available, send your request to projects at saturn.in visit Saturn SPL corporate site

  3. Viktor
    March 4th, 2010 at 23:08 | #3

    nice share ! is this an application of nested set concept?

  4. March 10th, 2010 at 23:34 | #4

    I would agree that this seems to fall in the same concept. But was written to assist in academic interests to help understand Binary Tree which is used heavily in Multilevel Marketing systems

  5. August 19th, 2010 at 21:43 | #5

    hi all
    i m unable to count left and right member
    my table looks like this
    ————————————————————-
    | id | parent id | placement type |
    ————————————————————-
    | 1 | 0 | – |
    | 2 | 1 | L |
    | 3 | 1 | R |
    | 4 | 2 | L |
    | 5 | 2 | R |
    | 6 | 3 | L |
    | 7 | 3 | R |
    ————————————————————-

    I Just want to count how much members are in left and right in 1st binary tree..

    binary tree—

    1
    / \
    2 3
    / \ / \
    4 5 6 7

    plz help

  6. September 1st, 2010 at 16:27 | #6

    Hi Sir,

    I used you concept for tree position. but i make one addition i have three child node [left,center,right], so all is set for me just one problem how to delete node so all position set properly.

  7. September 7th, 2010 at 21:57 | #7

    Binary or ternary, you cannot delete a node which is active. Unless you decide upon the priority rulez on who will go up and then you will have to rewrite the whole subtrees. which will make the operation a hell. In commercial chains, we normally coax the customer to accept the fact that such a dormant node ( unpaid or deactivated ) will be considered as a blackhole, through which the sponsoring company will get the benefits. But I am sure that does not solve your problem. Will need to sit and think on how this can be addressed.

  8. September 7th, 2010 at 22:07 | #8

    Sorry I left out the possibility of such a requirement at the time of generating the shown code, still it was as I said, an academic project and memories of past experiences. I will consider this when I have some free time, since now a days, I am too busy with Reserway projects, a whole bunch of demanding, pestering travel agents and related portals, nothing to say about Xeproof with a whole new span of clients with varying demands.

  9. Ikhlaque Ahmed
    October 25th, 2010 at 14:52 | #9

    Hello sir,

    I am trying to develope MLM site.But I can’t able to make a database please. Please send me a sample databse with example on my Email -ID please manerikhlaque@gmail.com

  10. March 9th, 2011 at 15:28 | #10

    Dear sir,

    Can u help me for retrieving left tree and right tree seperately by using procedure…

  11. March 9th, 2011 at 15:34 | #11

    Dear sir,

    I finished watever u gave. but i need left tree retrieval for a particular node and right tree also…

  12. March 29th, 2011 at 06:28 | #12

    @saravananr
    This can be achieved by what I have given here, provided the structure is same. First get the identification of the left and right node, then call getTreeFrom twice passing each node as the parameter.

  13. sant
    April 1st, 2011 at 14:53 | #13

    thnx a lot.
    saved my day !!!!

  14. May 6th, 2011 at 11:52 | #14

    Thanks for the post, it is very helpful for me, I am creating a MLM System
    I hope you will help me.
    regards

  15. June 4th, 2011 at 17:43 | #15

    Thanks sir for your post. It helped me a lot in my Project. I am a student want code to study. Please provide me the code…..

  16. July 19th, 2011 at 14:18 | #16

    All the code is available on this site with this post. This is not a full fledged application, but an insight towards how easy we can do this using the MySQL procedures.

  17. Sunitha
    September 20th, 2011 at 15:00 | #17

    Thanks for posting the useful procedures.I would like to have a documentation for this post.Can you please post if it there.Can you please post php scripts to use getTreeFrom procedure in binary tree format.

  18. Manisha
    October 4th, 2011 at 15:30 | #18

    how can i decide the levels of a binary tree and how to count no. of nodes per level in php using database Mysql.

  19. aniket
    November 13th, 2011 at 17:22 | #19

    hello sir,
    i want to know how to find the leaf node of my right or left side

  20. December 28th, 2011 at 17:14 | #20

    Hello sir, I am trying to make MLM tree using the code you provided, but I am not getting the result.

    Please can you provide me the a code in zip file with instruction to use it

  21. Gracia Ü Lurzano
    February 23rd, 2012 at 10:53 | #21

    bookmarked! :)

  22. Surya Kumar
    March 7th, 2012 at 15:06 | #22

    Hi..
    i want a mlm binary code and databse in php.. pls send me the source code to this mail suryaakumarrsc@gmail.com..

  23. Gopal Singh
    April 17th, 2012 at 13:12 | #23

    I am developing a website for multilevel marketing
    concept is – a client can sponsor many clients, these many clients make more clients ……
    Now i want to give some points to parent clients after adding child clients to it. These points should be added to parents’s parent cliets…upto starting root, but i am not able to give these points . Also i am not able to retrieve the records of root’s client,its client, its client….. upto child level
    Please help me
    Thanks

  24. April 23rd, 2012 at 22:53 | #24

    Great thought.. I have never thought about sending source code to email address. If you are interested, contact our office, and buy the code with support.

  25. Tajinder
    May 12th, 2012 at 15:42 | #25

    This is awesome tutorial. But runs only on localhost. Tried lots of ways to run these SP on Server using php script but they did not run. So can you please help me out.

  26. May 23rd, 2012 at 07:37 | #26

    The issues that could have prevented you from running on your server is not related to the SP code, it is rather a configuration issue or a version mismatch. You need to use the mysqli* functions to utilize the potential of SP. Also need to create the SP with proper permissions.

  27. praveen kumar dokanai
    July 20th, 2012 at 12:29 | #27

    Sir,
    I want to make a MLM website in php and for that I need your help that how to create database and generate binary system.kindly guide me.

  28. November 7th, 2012 at 12:19 | #28

    can you please send me your sample mlm binary file for php to my email address? Thanks

  29. simran
    January 18th, 2013 at 16:57 | #29

    Sir,
    I want to make a MLM website in php and for that I need your help that how to create database and generate binary system.
    please can you mail me the sample to my email id.
    thanks

  30. shalu
    January 22nd, 2013 at 00:07 | #30

    Sir,
    i want to get flow diagrame of Binary mlm web software,
    please send me on this email address

    thank you so much sir

  31. February 25th, 2013 at 05:51 | #31

    Why the flow diagram alone? This was an academic project and was created long back. What is available here is the limit.

  32. February 25th, 2013 at 05:55 | #32

    What is all these kids thinking now a days! Code and work are no more free. Any service needed contact the parent company.

  33. Anil Singh
    June 15th, 2013 at 17:34 | #33

    Dear Sir,

    I am making a website mlm… but i am create database successfully please send the code my email id…

    Thank,
    Anil Singh

  34. Anil Singh
    June 15th, 2013 at 17:34 | #34

    Dear Sir,

    I am making a website mlm… but i am not create database successfully please send the code my email id…

    Thank,
    Anil Singh

  35. drsht
    March 13th, 2014 at 20:57 | #35

    dear sir,
    I am making a website mlm… but i am not create database successfully please send the code my email id…
    Thank,

  1. No trackbacks yet.

seventy three + = eighty one