CODEWITHSUNDEEP
phpmysql
Deepu Sasidharan
Deepu Sasidharan

Reputation: 5309

Display folder structure using mysql

I need to list folder name and sub folder name using mysql query. The depth of subfolders is unknown. That is a parent folder may have n level of sub folders. Please refer the sample folder structue below:

.
├── PHP
│   ├── Developer
│   │   ├── Junior
│   │   └── Senior
│   └── Tester
├── Java
│   └── Test Engineer
└── Android

The folder structure may vary..

My table is look like

enter image description here

I want to load the folder name in a select box like parent and their child manner.

  PHP
  Developer
  Junior
  Senior
  Tester
  Java
  Test Engineer
  Android

What I tried?

$fOption="";
$parentFolders = $db->query("SELECT * FROM jp_emp_folder WHERE parent_id='0' AND emp_id='".$empId."'");
while($parentRows = $db->fetch_array($parentFolders)){
    $fOption .= "<option value='".$parentRows['id']."'>".$parentRows['folder_name']."</option>";
    $child = $db->query("SELECT * FROM jp_emp_folder WHERE parent_id='".$parentRows['id']."' AND emp_id='".$empId."'");
    while($childRows = $db->fetch_array($child)){
        $fOption .= "<option value='".$childRows['id']."'>".$childRows['folder_name']."</option>";
    }
} 
echo $fOption;

I know this only give the 2 level folder structure. But I have n level. How can I acheive this? Please suggest..

Upvotes: 1

Views: 1221

Answers (2)

Thanaruby Nanthagopal
Thanaruby Nanthagopal

Reputation: 614

Check this.

$all_folders = $db->query("SELECT * FROM jp_emp_folder WHERE emp_id='".$empId."'");
$array_column = array_column($all_folders, 'parent_id');        
$all_parent0_dir = array_keys($array_column, "0");  //get all base folders

foreach ($all_parent0_dir as $value) { 
    $dir = $all_folders[$value];
    echo '<option value="'.$dir['id'].'">'.$dir['foler_name'].'</option>';
    $parent_id = $dir['id'];
    $nodeNumber = 1;
    $lastElem = FALSE;//is this last element of the folder
    $nodeArray = [];//save all the nodes of the folder
    while ( $parent_id != $dir['id'] || $lastElem == FALSE) {
        /*************************************************************/
        //get details of a specific folder
        $last = 1;
        $sub_dir_ids = array_keys($array_column, $parent_id); 
        if($sub_dir_ids)
        { 
            $sub_dir = isset($sub_dir_ids[$nodeNumber-1])? $sub_dir_ids[$nodeNumber-1] : null;
            if($sub_dir){ 
                $res = $all_folders[$sub_dir];
                echo '<option value="'.$res['id'].'">'.$res['foler_name'].'</option>';
                if($res){$last = 0; }                       
            }
        }
        /*************************************************************/
        if($last == 1){           
            if($parent_id != $dir['id']){//if $parent_id didn't reach the first element (starting point)
                $lastElement = array_pop($nodeArray);
                $parent_id = $lastElement[0];
                $nodeNumber = $lastElement[1] + 1;
            }else{$lastElem = TRUE;}
        }else{
           $lastElem = FALSE;
           array_push($nodeArray, array($parent_id,$nodeNumber));
           $parent_id = $res['id'];//$parent_id for inner folder
           $nodeNumber = 1;//go to next step - inner folder
        }
    }            
}

Output

<option value="1">PHP</option>
<option value="5">DEVELOPER</option>
<option value="7">JUNIOR</option>
<option value="8">SENIOR</option>
<option value="6">TESTER</option>
<option value="2">JAVA</option>
<option value="4">TEST ENG</option>
<option value="3">ANDROID</option>

Upvotes: 0

Raptor
Raptor

Reputation: 54212

You can use this approach to construct your hierarchy.

Step 1: Build up the $data in PHP

$data = array();
$results = $db->query("SELECT * FROM jp_emp_folder WHERE emp_id=" . (int)$empId);
while($rows = $db->fetch_array($results)) {
  $data[$row['id']] = $rows;
}

Step 2: Recursively read the $data to construct hierarchy

function readData($parent_id = 0) {
        global $data; // I'm just lazy, avoid using "global"
        foreach($data as $id => $item) {
            if($parent_id > 0 && $item['parent_id'] != $parent_id) {
                continue;
            }
            echo '<option value="' . $id . '">' . $item['folder_name'] . '</option>' . PHP_EOL;
            unset($data[$id]); // Remove echo-ed contents
            if($item['parent_id'] > 0) {
                readData((int)$id);
            }
        }
    }

readData(); // Start reading $data

which will output the following:

<option value="1">PHP</option>
<option value="2">Java</option>
<option value="3">Android</option>
<option value="4">Test Engineer</option>
<option value="5">Developer</option>
<option value="7">Junior</option>
<option value="6">Tester</option>

Upvotes: 1

Related Questions