Extract from database elements inside elements and so on

Question

I want to extract for example from the database a few rows:

they have, for example, id, name, parent.

0, element1, noparent
1, element2, 0
2, element3, 1
3, element4, 2

and so on...

I want to output them one inside another (in html)

<div id=0 data-name=element1>
    <div id=1 data-name=element2>
        <div id=2 data-name=element3>
            <div id=3 data-name=element4>
            </div>
        <div>
    </div>
</div>

and there can be many possibilities. So what I need is how to output them one inside another relying on parent?

(again, (just if you didn't understand :)) they can be 100 one inside the other... how do I do this?)

Answer 1

Without going into a whole tutorial on how to get things out of your database, suffice it to say that it's important to use fetchObject rather than one of the methods that fetches an array in order for this approach to work:

$stmt = $pdo->query('SELECT id, name, parent FROM your_table');

while ($row = $stmt->fetchObject()) {
    $data[$row->id] = $row;
}

This will get you an array of stdClass objects using the id as key. Because of the way objects are passed around in PHP, it is much easier to construct the tree than it would be with a multidimensional array.

foreach ($data as $element) {
    if ($element->parent !== null) {
        $data[$element->parent]->children[] = $element;
    }
}
$tree = array_filter($data, function($x) { return $x->parent === null; });

If you have a hard time seeing what this is doing, you can var_dump the $data and $tree arrays at various points to see what they look like (or even better, use an actual debugger). This should not be as hard on your memory as it looks like it could be, because the things going into children[] are copies of the object identifiers rather than new copies of the entire objects. The array_filter step just limits the array to root level (no parent) elements.

After you have constructed the tree, you can output it in your desired format with a recursive function.

function outputTree($tree) {
    $output = '';
    foreach ($tree as $branch) {        
        $output .= "<div id='$branch->id' data-name='$branch->name'>";
        if (isset($branch->children)) {
            $output .= outputTree($branch->children);
        }
        $output .= '</div>';
    }
    return $output;
}

echo outputTree($tree);

I haven't tested this with anything close to 100 levels, but theoretically it works to indefinite depth. Please be careful when putting things into your database not to create things that are descendants of themselves, or you'll create a rift in the space-time continuum and destroy the universe, or at least your script.

Answer 2

UPDATED with an exact working example.

Your topic is something I am also struggling with. It has a title "multidimensional arrays" in association with mysql or mysqli.

Step 1: THE DB ..

CREATE TABLE `tbl_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '0',
  `parent_id` int(11) NOT NULL DEFAULT '0'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `tbl_categories` (`id`, `name`, `parent_id`) VALUES
 (1, 'element1', 0),
 (2, 'element2', 1),
 (3, 'element3', 2),
 (4, 'element4', 3);

As You see the parent_id is all you need to change in an item to change the hierarchy.

Step 2: A function to iterate through the entries...

function categoryParentChildTree($parent = 0, $category_tree_array = '') {
global $dbConnection;
$parent = $dbConnection->real_escape_string($parent);
if (!is_array($category_tree_array))     //###
    $category_tree_array = array();

$sqlCategory = "SELECT id,name,parent_id FROM tbl_categories WHERE parent_id = $parent ORDER BY id ASC";
$resCategory=$dbConnection->query($sqlCategory);

if ($resCategory->num_rows > 0) {
    while($rowCategories = $resCategory->fetch_assoc()) {
        $category_tree_array[] = array("id" => "<div id='".$rowCategories['id']."'", "name" => " data-name='".$rowCategories['name']."'>{$rowCategories['name']}");
        $category_tree_array = categoryParentChildTree($rowCategories['id'], $category_tree_array);
    }
}
return $category_tree_array;

}

STEP 3 : Using the function

   $categoryList = categoryParentChildTree();
foreach($categoryList as $key => $value){
    echo $value['id']." ".$value['name'];
}

The very important line here is the one I marked with ###. The aregument there is to check wether the passed variable is an array.In case it is not, the function continues. In case it is an array, the function calls itself on that same variable to flatten it and turn it into a variable in the process.

The output of this whole thing is as follows:

<html>
<head>
</head>
<body>
<div id="1" data-name="element1">element1
    <div id="2" data-name="element2">element2
        <div id="3" data-name="element3">element3
            <div id="4" data-name="element4">element4
            </div>
        </div>
    </div>
</div>
</body>
</html>

Theoretically this deals with infinite levels of nesting and items. I hope it helps.

This is based on a tutorial by Saurabh Kumar Singh. You can see the fully functional original demo and the downloadable zip from the link: http://demo.stepblogging.com/category-tree/

Finally if this addresses your needs please don't forget to accept the answer.

Answer 3

query the db. the results can be stored in an array. Then iterate through the array and return the results.