Show category table with parent categories

Question

Below is my table structure:

Menu table

id title position -------------------- 1 Test home 2 Test2 home 

Category

 cid name parent parent_menu -------------------------------- 1 ABC 0 1 2 DEF 0 2 3 GHI 1 0 4 JKL 2 0 

Category Description

 id cat_id catdesc slug ------------------------------- 1 1 ABC_DESC abc 2 2 DEF_DESC def 3 3 GHI_DESC ghi 4 4 JKL_DESC jkl 

• Menu table menu Menu table treats the menu name as a position.
• Category table category Category table processes the category name and other parameters. (if parent = 0, then this means that this is the main category, etc.)
• Category Description table handles the description, slug, and other parameters.

Now I want to display the data as shown below

  Name Description Edit Delete /*table headings*/ ------------------------------------------------------- Menu Title: (Test) Main Category Name: (ABC) ------------------------------------------------------ GHI GHI_DESC edit_icon delete_icon ______________________________________________________ Menu Title: (Test2) Main Category Name: (DEF) ------------------------------------------------------ JKL JKL_DESC edit_icon delete_icon 

I tried using JOINS and manipulating data in PHP, but no luck.

 SELECT * FROM `category` t1 LEFT JOIN `category_description` t2 ON t1.cid = t2.cat_id WHERE 1 

Then in PHP I tried as below

 <?php $i = 1; foreach($subcat as $sub) { ?> <?php if($sub->parent == 0) { ?> <tr><td><?php echo $sub->name ?></td></tr> <?php } ?> <?php if($sub->parent != 0) { ?> <tr><td><?php echo $sub->name ?></td><td><?php echo $sub->catdesc ?></td> <td>Edit</td><td>Delete</td></tr> <?php } ?> <?php } ?> 

And above prints the table as below:

 Main Category Name: ABC Main Category Name: DEF ------ GHI GHI_DESC JKl JKL_DESC 

Please suggest how to print as needed.

Answer 1

Assuming parent_menu has an identifier in the menu table, try the following:

 SELECT t1.title, t2.name, t2.parent, t2.parent_menu, t3.catdesc FROM menu t1 LEFT JOIN category t2 ON t1.id=t2.parent_menu LEFT JOIN description t3 ON t2.cid=t3.cat_id GROUP BY t2.name 

sql demo and php demo

Answer 2

you can use the old MySQL method like:

 select mt.title, cat.name, cdesc.catdesc from menu_title mt, category cat, category_description cdesc where mt.id = cat.parent_menu and cat.cid = cdesc.cat_id 

Answer 3

I would recommend you build a tree and process the tree using ArrayIterator and RecursiveIteratorIterator.
To build a tree, you can customize this sample code:

 $tree = array(); foreach($result_set as $result) { if ($result["parent"] == 0) { $tree["cat"][$result["cid"]]["parent"] = $result; } else { $tree["cat"][$result["parent"]]["child"][$result["cid"] = $result; } } 

Remember: you need to configure it!
Then you can implement the iterator as follows:

 class CategorieTreeIterator extends ArrayIterator implements RecursiveIterator { public function getChildren() { $link_data = $this->current(); $cid = key($link_data["cat"]); return new CategorieTreeIterator($link_data["cat"][$cid]["child"]); } public function hasChildren() { $link_data = $this->current(); $cid = key($link_data["cat"]); return !empty($link_data["cat"][$cid]["child"]); } } 

Again, you need to configure it.
To display the data, you can now iterate over usage:

 $cat_tree_iter = new CategorieTreeIterator($tree); $rec_iter_iter = new RecursiveIteratorIterator($cat_tree_iter, RecursiveIteratorIterator::SELF_FIRST); foreach($rec_iter_iter as $iter) { //display data with help of $iter->getDepth(); }