I am trying to get my all categories and sub-categories from MySQL database in a hierarchy:
My result should be like that (just example):
- Cat A
- Sub-Cat 1
- Sub_Sub_Cat 1
- Sub_Sub_Cat 2
- Sub_Cat 2
- Cat B
- Cat C
- ...
MySQL code:
CREATE TABLE IF NOT EXISTS `categories` (
`category_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'for sub-categories'
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Simply, how can get it in a hirarchy with PHP codes?
There's another way to achieve the same effect which I find a bit easier to follow (without the reference trick). You build the tree by adding the relevant information to the current node and to its parent (assume the foreach iterates over the returned rows from the SQL query):
and to display the tree:
@Amnon Your code works perfectly. Just tested it with CodeIgniter and it worked like a charm. Here's the working code if anyone needs it:
The only thing I changed was adding my own array ($all_cats).
Try the following code
//connect to mysql and select db
$conn = mysqli_connect('localhost', 'user', 'password','database');
More...
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):
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.
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.
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:
Now lets make your desired queries:
1- all sub categories of cars:
2- if you need all parent of BLACK you simply type:
(you can build that query from php, splitting hierarchy field at '-' char)
3- To see all categories, with level and direct parent:
This is a new idea and need some improvement.