Category Hierarchy (PHP/MySQL)
When using an adjacency list model, you can generate the structure in one pass.
Taken from One Pass Parent-Child Array Structure (Sep 2007; by Nate Weiner):
$refs = array();$list = array();$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";/** @var $pdo \PDO */$result = $pdo->query($sql);foreach ($result as $row){ $ref = & $refs[$row['item_id']]; $ref['parent_id'] = $row['parent_id']; $ref['name'] = $row['name']; if ($row['parent_id'] == 0) { $list[$row['item_id']] = & $ref; } else { $refs[$row['parent_id']]['children'][$row['item_id']] = & $ref; }}From the linked article, here's a snippet to create a list for output. It is recursive, if there a children for a node, it calls itself again to build up the subtree.
function toUL(array $array){ $html = '<ul>' . PHP_EOL; foreach ($array as $value) { $html .= '<li>' . $value['name']; if (!empty($value['children'])) { $html .= toUL($value['children']); } $html .= '</li>' . PHP_EOL; } $html .= '</ul>' . PHP_EOL; return $html;}Related Question:
I have a new idea I think it will be nice.The idea is this:in category_parent column we will insert a reference to all parents of this node.
+----+----------------------+-----------------+| id | category_name | hierarchy |+----+----------------------+-----------------+| 1 | cat1 | 1 |+----+----------------------+-----------------+| 2 | cat2 | 2 |+----+----------------------+-----------------+| 3 | cat3 | 3 |+----+----------------------+-----------------+| 4 | subcat1_1 | 1-4 |+----+----------------------+-----------------+| 5 | subcat1_2 | 1-5 |+----+----------------------+-----------------+| 6 | subsubcat1_1 | 1-4-6 |+----+----------------------+-----------------+| 7 | subsubcat1_2 | 1-4-7 |+----+----------------------+-----------------+| 8 | subsubcat1_3 | 1-4-8 |+----+----------------------+-----------------+| 9 | subsubcat1_3_1 | 1-4-8-9 |+----+----------------------+-----------------+| 10 | subsubcat1_3_2 | 1-4-8-10 |+----+----------------------+-----------------+| 11 | subsubcat1_3_1_1 | 1-4-8-9-11 |+----+----------------------+-----------------+| 12 | subsubsubcat1_3_1_1 | 1-4-8-9-12 |+----+----------------------+-----------------+| 13 | subsubsubcat1_3_1_2 | 1-4-8-9-11-13 |+----+----------------------+-----------------+| 14 | subsubsubcat1_2_1_3 | 1-4-8-9-11-14 |+----+----------------------+-----------------+
if you look at my updated table you will notice that every record has an link to its parents, not only the direct one, But also all of parents.And for that job I made some modification to insert to be:
Insert into table_name (category_name, hierarchy) values ('new_name', (concat(parent_hierarch, '-', (SELECT Auto_increment FROM information_schema.tables WHERE table_name='table_name'))))Now lets make your desired queries:
1- all sub categories of cars:
select * from table_name where hierarchy like '1-%'2- if you need all parent of BLACK you simply type:
select * from table_name where hierarchy = '1-4-8-9' or hierarchy = '1-4-8' or hierarchy = '1-4' or hierarchy = '1'(you can build that query from php, splitting hierarchy field at '-' char)
3- To see all categories, with level and direct parent:
select *, SUBSTR(hierarchy, 1, (LENGTH(hierarchy) - LENGTH(id) - 1)) as parent, LENGTH(hierarchy) - LENGTH(REPLACE(hierarchy, '-', '')) as level From table_name+----+----------------------+-----------------+-----------+--------+| id | category name | hierarchy | parent | level |+----+----------------------+-----------------+-----------+--------+| 1 | cat1 | 1 | | 0 |+----+----------------------+-----------------+-----------+--------+| 2 | cat2 | 2 | | 0 |+----+----------------------+-----------------+-----------+--------+| 3 | cat3 | 3 | | 0 |+----+----------------------+-----------------+-----------+--------+| 4 | subcat1_1 | 1-4 | 1 | 1 |+----+----------------------+-----------------+-----------+--------+| 5 | subcat1_2 | 1-5 | 1 | 1 |+----+----------------------+-----------------+-----------+--------+| 6 | subsubcat1_1 | 1-4-6 | 1-4 | 2 |+----+----------------------+-----------------+-----------+--------+| 7 | subsubcat1_2 | 1-4-7 | 1-4 | 2 |+----+----------------------+-----------------+-----------+--------+| 8 | subsubcat1_3 | 1-4-8 | 1-4 | 2 |+----+----------------------+-----------------+-----------+--------+| 9 | subsubcat1_3_1 | 1-4-8-9 | 1-4-8 | 3 |+----+----------------------+-----------------+-----------+--------+| 10 | subsubcat1_3_2 | 1-4-8-10 | 1-4-8 | 3 |+----+----------------------+-----------------+-----------+--------+| 11 | subsubcat1_3_1_1 | 1-4-8-9-11 | 1-4-8-9 | 4 |+----+----------------------+-----------------+-----------+--------+| 12 | subsubsubcat1_3_1_1 | 1-4-8-9-12 | 1-4-8-9 | 4 |+----+----------------------+-----------------+-----------+--------+| 13 | subsubsubcat1_3_1_2 | 1-4-8-9-11-13 |1-4-8-9-11 | 5 |+----+----------------------+-----------------+-----------+--------+| 14 | subsubsubcat1_2_1_3 | 1-4-8-9-11-14 |1-4-8-9-11 | 5 |+----+----------------------+-----------------+-----------+--------+
This is a new idea and need some improvement.
Try the following code
//connect to mysql and select db
$conn = mysqli_connect('localhost', 'user', 'password','database');
if( !empty($conn->connect_errno)) die("Error " . mysqli_error($conn));//call the recursive function to print category listingcategory_tree(0);//Recursive php functionfunction category_tree($catid){global $conn;$sql = "select * from category where parent_id ='".$catid."'";$result = $conn->query($sql);while($row = mysqli_fetch_object($result)):$i = 0;if ($i == 0) echo '<ul>'; echo '<li>' . $row->cat_name; category_tree($row->id); echo '</li>';$i++; if ($i > 0) echo '</ul>';endwhile;}//close the connectionmysqli_close($conn);?>