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
– Aerik, Commented Dec 15, 2011 at 17:47
@andho not the full tree each time but yes all children of some root category are needed often — lovesh
– lovesh, Commented Dec 17, 2011 at 4:43

4 revs, 3 users 88% · Accepted Answer · 2022-10-21 23:18:53Z

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, 2022 at 23:18

community wiki

4 revs, 3 users 88%
Bill Karwin

Sign up to request clarification or add additional context in comments.

3 Comments

andho Over a year ago

I would also add a most refreshing slideshow: slideshare.net/billkarwin/sql-antipatterns-strike-back

Bill Karwin Over a year ago

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"

andho Over a year ago

LOL, I didn't realise it was yours!

Cybercartel · Accepted Answer · 2011-12-16 16:09:27Z

0

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>';

answered Dec 16, 2011 at 16:09

Cybercartel

12.6k7 gold badges39 silver badges73 bronze badges

Comments

Meloman · Accepted Answer · 2020-08-25 12:37:03Z

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

Collectives™ on Stack Overflow

querying a hierarchical mysql table in php

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related