0

I am trying to make selectable options with "infinity depth". I created the tables however I am unable to fetch them into selectpicker. How should I do it?

SECTORS in my case are Categories

This is what I've done:

CREATE TABLE sectors(
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent_id INT DEFAULT NULL
);

CREATE TABLE users(
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        is_agreed TINYINT NOT NULL
);

CREATE TABLE selected_sectors(
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        selected_sector_id INT NOT NULL

);

This makes the selections but subcategories are lost like this:

public function getAllSectors(){
    $statement = $this->connect()->query("SELECT * FROM sectors");
    while ($row = $statement->fetch()){
        $section_id = $row['id'];
        $name = $row['name'];
        $parent_id = $row['parent_id'];


        if ($parent_id == NULL){
            echo '<option value="'.$section_id.'">   '.$name.'</option>';
        }elseif($parent_id == $section_id){
            echo '<option value="'.$section_id.'">'.$name.'</option>';
        }
    }
}

What should I do? Any response is appreciated.

Tarvo Mäesepp
  • 4,477
  • 3
  • 44
  • 92
  • *"This makes the selections but without indent"* ... well, yes? You're just spaffing out ` – CD001 Feb 16 '18 at 13:51
  • @CD001 I tried but what if the sub category has category? The indent is going wrong like this. – Tarvo Mäesepp Feb 16 '18 at 13:56
  • The problem you've got is that you don't *really* know what level any sector is at without running a recursive query to find all of its ancestors, so you don't know how much to indent by. You'll run into additional problems as you add and delete sectors since you've no way of constraining the order in which they're read from the database. I did something similar with a dropdown navigation system a long time ago... ended up using DOMDocument to structure the data in XML before parsing it - that way "children" really were children; nested in their parent nodes. – CD001 Feb 16 '18 at 14:18

3 Answers3

0

You should have materialized path in your DB for many reasons

CREATE TABLE sectors(
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        tree VARCHAR(255) DEFAULT '',
        parent_id INT DEFAULT NULL
);

where tree should have values like '{sector_id_depth_0}/{sector_id_depth_1}/{sector_id_depth_2}/{sector_id_depth_3}/{sector_id}'

You must not do recursive queries to generate your select (with ORDER BY tree) and many other thing you should do with this tree. You may know its depth ($depth = count(explode('/', $treeColumnValue)); ) and ancestors without querying database.

And generate your depth as option in ui :

<option value="<?= $id ?>"><?php echo str_repeat('-', $depth).' '.$name; ?>
Oulalahakabu
  • 504
  • 3
  • 6
  • I actually disagree with this on the grounds that it de-normalises your data structure - your ids are no longer truly atomic as they're embedded, as text, in that path. If you ever delete a sector, or rejig the structure you'll need the application to deal with updating the path. I have seen this approach taken several times before though. – CD001 Feb 16 '18 at 14:52
  • Materialized path is a common use, some people may disagree with this model (and i respect this opinion) but 99% of uses on internet, when generating select as the topic author, correspond to this model. Is dealing with path really painful ? ;) – Oulalahakabu Feb 16 '18 at 15:04
0

You have n level of categories so you need to run recursive function.

First thing you should run all data from database and store in array. I think it's not good to call data every time from database.

<?php

// Categories Tree

function get_category_list($data, $pk = 'parent', $key = 'id', $sub_c = 'sub_categories')
    {
    $cats_tree = array();
    foreach($data as $d)
        {
        isset($cats_tree[$d[$pk]]) ? : $cats_tree[$d[$pk]] = array();
        isset($cats_tree[$d[$key]]) ? : $cats_tree[$d[$key]] = array();
        $cats_tree[$d[$pk]][$d[$key]] = array_merge($d, array(
            $sub_c => & $cats_tree[$d[$key]]
        ));
        }

    return $cats_tree[0];
    }

// make categories in option

function makeoptions($Array)
    {
    foreach($Array as $k => $v)
        {
        $Output.= "<option>";
        $Output.= $v["title"];
        if (is_array($v["children"]))
            {
            $Output.= makeNestedList($v["children"]);
            }

        $Output.= '</option>';
        }

    return $Output;
    }

// call functions

$query_categories = $mysqli->pepare("SELECT `id`,... FROM table");

// get data in array format  from database

$reStruct = array();

foreach($query_categories as $k => $v)
    {
    $reStruct[$v["id"]] = array(
        "id" => $v["id"],
        "name" => $v["name"],
        "parent" => $v["parent"],
    );
    }

$a = get_category_list($reStruct);
$x = makeoptions($a);
$html = "<select>";
$html.= $x;
$html.= "</select>";
echo $html;
Mourad Karoudi
  • 348
  • 3
  • 13
0

This is what I did:

function getAllSectors(&$output = null, $parent_id = 0, $indent = null){

    $statement = $this->connect()->prepare("SELECT id, name FROM sectors WHERE parent_id =:parent_id ORDER BY parent_id, sort_order");

    $statement->execute(array(
        'parent_id'     => $parent_id
    ));

    // show the sectors one by one
    while($row = $statement->fetch()){
        $output .= '<option value=' . $row['id'] . '>' . $indent . $row['name'] . "</option>";
        if($row['id'] != $parent_id){
            // In case the current sectors's id is different than $parent_id
            // I call the function again with new parameters(so it is recursive)
            $this->getAllSectors($output, $row['id'], $indent . "&nbsp;&nbsp;");
        }
    }
    // return the list of sectors(call out is in index.php)
    return $output;
}
Tarvo Mäesepp
  • 4,477
  • 3
  • 44
  • 92