Building Multi Level Menu Dynamically with PDO in PHP

Question

I have this data in my database:

id  nameOfPerson    parent
3   John             NULL
4   Michel            3
5   Husam             4
6   Khalaf            5
7   Mark              5
----------------------------

and i want to display it in list like this

John
- Michel
  - Husam
    - Khalaf
    - Mark

but in my code he just displayed

John
- Michel

How i want to Displeyed all data according to parent like above list ?

this is my function whats the wrongs ?

  public function familyTree(){
    $query = "SELECT id, nameOfPerson, parent FROM person WHERE parent is null";
    $statment = $this->db->prepare($query);
    $statment->execute();
    echo '<ul id ="family">';
    while($family = $statment->fetch(PDO::FETCH_OBJ)){
        echo '<li>'. $family->nameOfPerson;
        $query1 = "SELECT id, nameOfPerson, parent FROM person WHERE parent = :id";
        $statment1 = $this->db->prepare($query1);
       $statment1->bindValue('id', $family->id);
        $statment1->execute();
        if($statment1->rowCount() > 0){
        echo '<ul>';
        while($family2 = $statment1->fetch(PDO::FETCH_OBJ)){
            echo '<li>' . $family2->nameOfPerson . '</li>';
        }
            echo '</ul>';
        }
            echo '</li>';
    }
            echo '</ul>';      

}

I would encurage you to first get all the data out of database and write it in a well structured array. Then build the html of that with a recursive function. — Jeff
– Jeff, Commented May 29, 2018 at 9:11
Thanx for your advice, i will learning what you encourage me. I 'am fresh self learning, my question is did there solution to solve this problem with out change the code now ? — Second View
– Second View, Commented May 29, 2018 at 9:19
you defenitely need a recursive function (a function that calls itself for all the children) — Jeff
– Jeff, Commented May 29, 2018 at 9:21
@Jeff i make the parent foreign key from the id ... that's a good step or not ? — Second View
– Second View, Commented May 29, 2018 at 9:34

mighty_dev · Accepted Answer · 2018-05-29 13:47:55Z

ASSUMPTIONS

Every child has only 1 parent.

CODE

First, I retrieve all data from DB table as it is not efficient to retrieve data individually for each parent increasing DB calls and affecting response time.
Build an associative array of parent and children where every parent has 2 details of each child - nameOfPerson and id.
Iterate over this array in a recursive way and keep building the list for each child and ultimately return them.

Refer SQL Fiddle for table rows.

<?php

$pdo = new PDO("mysql:dbname=newdummy;host=localhost","root","");

$query = $pdo->query("Select * from hierarchy");

$family_tree = [];
$root_parent = -1;
$root_parent_name = "";

function makeTree($query,&$family_tree,&$root_parent,&$root_parent_name){
   while($row = $query->fetch(PDO::FETCH_ASSOC)){
      if(is_null($row['parent'])){
          $root_parent = $row['id'];
          $root_parent_name = $row['nameOfPerson'];
      }else{
          if(!isset($family_tree[$row['parent']])){
            $family_tree[$row['parent']] = [];
          }          
          $family_tree[$row['parent']][] = array($row['nameOfPerson'],$row['id']);
      }    
   }
}

function buildList($family_tree,$parent){
  $list = "<ul>";

  foreach($family_tree[$parent] as $each_child){
     $list .= "<li>" . $each_child[0];
     if(isset($family_tree[$each_child[1]])){
        $list .= buildList($family_tree,$each_child[1]);
     }
     $list .= "</li>";
  }

  $list .= "</ul>";

  return $list;
}


makeTree($query,$family_tree,$root_parent,$root_parent_name);


echo "<ul>";
echo "<li>$root_parent_name";
echo buildList($family_tree,$root_parent);
echo "</li>";
echo "</ul>";

OUTPUT

<ul>
    <li>John
        <ul>
            <li>Michel
                <ul>
                    <li>
                        Husam
                            <ul>
                                <li>khalaf</li>
                                <li>Mark</li>
                            </ul>
                    </li>
                </ul>
            </li>
            <li>Tross</li>
            <li>David</li>
        </ul>
    </li>
</ul>

Collectives™ on Stack Overflow

Building Multi Level Menu Dynamically with PDO in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related