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 handles the menu title an position.
• Category table handles category name and other parameters. (if parent=0 then it means that this is main category and so on..)
• Category Description table handles the description, slug and other parameters.

Now i want to display data like as 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 to use JOINS and manipulate 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 like 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 table like as below:

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

Please suggest how to print as desired.

Answer 1

I would recommend to you to build a tree and than iterate over the tree with help of the ArrayIterator and RecursiveIteratorIterator.
To build your tree you can adjust this code sample:

$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 have to adjust it!
Then you can implement the iterator like so:

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 have to adjust it.
To display the data, you can now iterate over use this:

$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();

}

Answer 2

Assuming parent_menu is from id in menu table, try this:

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 3

you can use old MySQL method as:

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