How to make arrangement in the Treeview with PHP and SQL？

Question

I am building my tree view. I am facing a problem to arrange data from SQL query browser database to insert data into the Treeview with PHP. When I pick up data from the database into my treeview, it shows me many duplicate data into my treeview. Another problem is how to change style in the treeview? Hope someone can help me to check which part I get wrong. Thanks.

Below is my coding:

<div class="row-fluid">              
<!-- block -->
<div class="block">

    <div class="block-content collapse in">
        <div class="span6"> 
            <?php
                $sql="select * from level_tree lt JOIN users u ON lt.user_id = u.id where lt.referal_id =". $user_id;
                $query=mysql_query($sql);
                if(mysql_num_rows($query)>0){
                    $select_name = 'SELECT * FROM users WHERE id = ' . $user_id;
                    $query_select = db_conn_select($select_name);
    foreach($query_select as $rs_select) {
   $name = $rs_select['name'];
   $email = $rs_select['email'];

     }
                ?>          
             <div id="jstree">
                <ul>
                    <li><?php echo $name. '('.$email.')' ?></li>

                <ul>
                <?php 

                while($rs=mysql_fetch_array($query)){
                    echo "<li>".$rs['name']."&nbsp;(".$rs['email'].")";
                    downline_list($rs['id']);
                    echo "</li>";
                }

            ?>
              </div>
             <?php 
                }else{
                    echo "No downline";
                }
                function downline_list($id){
                    $sql="select * from level_tree lt JOIN users u ON lt.user_id = u.id where lt.referal_id =".$id;
                    $query=mysql_query($sql);
                    if(mysql_num_rows($query)){
                        echo "<ul>";
                        while($rs=mysql_fetch_array($query)){               
                            echo "<li>".$rs['name']."&nbsp;(".$rs['email'].")";
                            downline_list($rs['id']);
                            echo "</li>";
                        }
                        echo "</ul>";
                    }
                }

            ?>               
        </ul></div>
        </div>
    </div>
    <!-- /block -->
  </div>

  <script src="plugins/jstree/dist/jquery-1.10.2.min.js"></script>
 <link rel="stylesheet" href="plugins/jstree/dist/themes/default/style.min.css" />
<script src="plugins/jstree/dist/jstree.min.js"></script>
<script>
$(function () {
// 6 create an instance when the DOM is ready
$('#jstree').bind("ready.jstree", function () {
    $('#jstree').jstree('open_all');
  }).jstree();

}); 
</script>
<style type="text/css">
  .jstree li > a > .jstree-icon {  display:none !important; } 
 </style>

I want to get data from database, I have 2 table ("users" and "level_tree") in my database below is my database information:

Table "users"(In this table I am using name is "developer2" account, so the top 1 in the tree name is select developer2, and this table I want to get column "name" and "email" data show in my treeview. This table"referal_id" number means under which person "name". For example: In this table example if name "tong" "referal_id" is "8", So that, name"tong" upline is "Ooi Thong Bee", because name "Ooi Thong Bee" "id" is "8". "referal_id" is get from "id" ):

Table "level_tree"(In this table, "referal_id" = 3 means that is under my account name"developer2", because in the table "users", name"developer2" is "id = 3". Column "level" means that see the "user_id" stand which level):

The output show me like the below picture:

Actually I want the output data arrange and treeview style in the treeview same like below picture:

Answer 1

Logic wise, your level_tree table is not necessary. You can deduce the whole thing directly form the users table.

Ex.

• Terry's referral is Ooi Thong Bee.
• Ooi Thong Bee's referral is developper2.

So no need for another table.

There are inconsistencies in your tables. In users table you have Hu La La referred by no one, in level_tree it is set to 3. Which one is the good one?

What you should do:

1) put all the referal_id values in the users table.
2) get rid of the level_tree table.
3) build your tree view display like this:

• Decide which user is the top of the tree. Like you did, lets say developper2.

• Find all the users referred by developper2

  select id from users where referal_id = (select id from users where name = 'developper2').

You will get Ooi Thong Bee and Hong Tian Fa. When you display these two, add 1 level of indentation.

• Then get the referred users of Ooi Thong Bee, then the referred users of Hong Tian Fa, and so on and so on. The idea is that you get the referred users of all users, as you "descend" into the tree.

• This could be done using a recursive function. Each time you find a child, call the recusive function again and increase the indent by the number of times the function has been called. This is called tree traversal (when your data is actually arranged in a tree. Here is is in a database table, but the relationship between users defines a table).

All this to say that you have to fix the data first, then build an algorithm around that data structure.

As far as your code:

• $userid is used in your first query, but it is never set (in the code you show anyway).
• your downline_list function is the right idea. Just pass it the level you are currently in. This way you know what indent to apply (level * 4 spaces). Before calling downline_list again, increment the indent.
• Finding out what the code is doing, and why is rather easy. Add print statements all around and look at what it is doing in each iteration. Ex. users 111, 222 and 333 should not be found as referred by tong. Only by fff. So print the query parameters and results to see what is going on.

Good luck.