Gags Gags - 7 months ago 6
PHP Question

Show Category table with parent categories

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

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

Demo here

Comments