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

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