querying a hierarchical mysql table in php

Question

i have a table with 3 columns id,name and parent_id representing categories. The root categories(categories with no parents) have parent_id 0. All other categories have parent_id as the id of their immediate parent. There is no limit on the depth of categories i mean that a category can be 3,4 or even 10 levels down from a root category. What i need now is a PHP multi-dimensional array that contains all the root categories at the first level and then their immediate sub-categories at the next level,each sub-category under its parent category,and their sub-categories under them 1 level down. So its a tree like structure. there can be many levels in the tree

I dont need the exact code but need an idea. one such idea is get all the root categories with a select query and then fire a select query for each root query to get its subcategoies and so on recursively but that would be too much select queries.

Or if i know that my table is going to contain say 300 rows at the maximum, how can i do something like

$categories=GetResultAsArray(select * from categories);

and now manipulate the $categories array in memory to get the desired tree.

I think both of your ideas are fine, and both are limited by the number of rows of data you have. How much a consideration is performance? Consider caching the resulting tree? You could also halve the number of sql queries by doing a single self join with each query - meaning, get all the parents and their children one level deep. — Aerik, Dec 15 '11 at 17:47
@andho not the full tree each time but yes all children of some root category are needed often — lovesh, Dec 17 '11 at 04:43

Bill Karwin · Accepted Answer · 2022-10-21T23:18:53.453

3

You're right, using the solution with a "parentid" column is simple but it makes you write recursive queries. There are several other designs for storing hierarchical data in a database that let you do queries more efficiently.

See:

What is the most efficient/elegant way to parse a flat table into a tree?
My other answers to SO questions on hierarchical-data
My presentation Models for Hierarchical Data with SQL and PHP
My book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming
Sql Antipatterns Strike Back

edited Oct 21 '22 at 23:18

answered Dec 16 '11 at 15:14

Bill Karwin

538,548
86
673
828

I would also add a most refreshing slideshow: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back – andho Dec 25 '11 at 03:29
Thanks @andho, the content about hierarchical data in the "SQL Antipatterns Strike Back" is a subset of the other presentation, "Models for Hierarchical Data with SQL and PHP" – Bill Karwin Dec 25 '11 at 07:30
1

LOL, I didn't realise it was yours! – andho Dec 25 '11 at 11:46

score 0 · Answer 2 · answered Aug 25 '20 at 12:37

You can be inspired by this solution that retrieves the breadcrumb trail of a url and its parents (infinite depth of hierarchy levels).

$q = "SELECT T2.*
    FROM (
    SELECT
        @r AS parent_id,
        (SELECT @r := `parent` 
        FROM `route` WHERE id = parent_id) 
    AS `parent`, @l := @l + 1 AS `depth`
    FROM
        (SELECT @r := $route_id, @l := 0) vars, `route` T3
        WHERE @r <> 0) T1
    JOIN `route` T2
    ON T1.parent_id = T2.id
    ORDER BY T1.`depth` DESC";

if($res = $db->query($q)) {
    if($res = $res->fetchAll(PDO::FETCH_ASSOC)) {
        if($size = sizeof($res) > 0) { 
            
            // push results in breadcrumb items array
            $breadcrumb['items']  = $res;
            $breadcrumb['levels'] = $size;
            
            // retrieve only titles
            $titles = array_column($res, 'title');

            // construct html result seperated by '>' or '/'
            $breadcrumb['html']['gt'] = implode(' > ', $titles);
            $breadcrumb['html']['sl'] = implode(' / ', $titles);
        }
    }
}

Entire get_breadcrumb() function available here : https://stackoverflow.com/a/63578607/2282880

score 0 · Answer 3 · answered Dec 16 '11 at 16:09

It's possible duplicate (http://stackoverflow.com/questions/8431463/more-efficient-hierarchy-system/8431551) but here is a snippet to query an adjacent tree (parent_id,id,title):

$q = mysql_query("SELECT id, parent_id, name FROM categories");
while ($r = mysql_fetch_row($q)) {
  $names[$r[0]] = $r[2];
  $children[$r[0]][] = $r[1];
 }

function render_select($root=0, $level=-1) {
  global $names, $children;
  if ($root != 0)
    echo '<option>' . strrep(' ', $level) . $names[$root] . '</option>';
  foreach ($children[$root] as $child)
    render_select($child, $level+1);
}

echo '<select>';
render_select();
echo '</select>';

querying a hierarchical mysql table in php

3 Answers3