Gags Gags - 2 years ago 72
PHP Question

Show Category table with parent categories

Below is my table structure:

Menu Table

id title position
1 Test home
2 Test2 home


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>
<?php } ?>
<?php } ?>

And above prints table like as below:

Main Category Name: ABC
Main Category Name: DEF

Please suggest how to print as desired.

Answer Source

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

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

Demo here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download