Fetch data from Hierarchical Data in MySQL

Question

I store multi level category. The table look like this:

Categories Table

+-------------+---------------+--------------------+
| category_id | category_name | parent_category_id |
+-------------+---------------+--------------------+
|           1 | Clothing      | NULL               |
|           2 | Footwear      | NULL               |
|           3 | Men           | 1                  |
|           4 | Women         | 1                  |
|           5 | Shirts        | 3                  |
|           6 | T-Shirts      | 3                  |
|           7 | Jeans         | 3                  |
+-------------+---------------+--------------------+

company_category_mapping Table

+------------+------------+-------------+
| cmp_cat_id | company_id | category_id |
+------------+------------+-------------+
|          1 |          7 |           5 |
|          2 |          7 |           6 |
|          3 |          7 |           7 |
+------------+------------+-------------+

I want to fetch all category, sub-category, sub-sub-category from category table with respect of company_id. I am confused how do this. can i create a new table for store company category?

I want to display output in drop-down. I have three drop-down one is main category dropdown, second is sub-category drop-down and third is sub-sub-category drop-down. Drop-down is fill companies wise i.e different company has different category.

Output display screen.

This is my output display category

Sorry for my English.

Thanks for helping me.

Can you put the out put that you expect? use ozh.github.io/ascii-tables — KTAnj
– KTAnj, Commented Jun 12, 2015 at 8:43
Why dont you create three loops. First will show all main category where parent category is null and companyid is given . and second loop will be depending on the result of first loop categoryids. — Hraday Joshi
– Hraday Joshi, Commented Jun 12, 2015 at 9:15
StackOverflow is not a place where you can post your code, and ask people to write code for you. You'll have to try something, and show what you're getting stuck on. — Mave
– Mave, Commented Jun 12, 2015 at 9:19

KTAnj · Accepted Answer · 2015-06-12 09:50:28Z

1

Try this code.

  SELECT c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
    LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
    LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
    where c1.`parent_category_id` IS NULL

With respect of company_id

SELECT cm.`company_id`, c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
INNER JOIN `company_category_mapping` as cm ON cm.`category_id`=c3.`category_id`
    where c1.`parent_category_id` IS NULL

If you need to get data for specific company_id.

Ex : company_id =7

SELECT cm.`company_id`, c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
    LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
    LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
    INNER JOIN `company_category_mapping` as cm ON cm.`category_id`=c3.`category_id` AND cm.`company_id`=7
        where c1.`parent_category_id` IS NULL

edited Jun 12, 2015 at 9:50

answered Jun 12, 2015 at 8:54

KTAnj

1,3561 gold badge15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Krishna Kumar Yadav Over a year ago

Thanks u for reply. But I want to display category with respect of company_id.

Krishna Kumar Yadav Over a year ago

Thank a lot of u. Its works for me. @KTAnj. One changes in this code is parent_category_name should be parent_category_id

Bruce · Accepted Answer · 2015-06-12 09:16:58Z

0

Try this code

i am using mysqli to avoid problems and hacking

$results = $mysqli->query( "SQL
SELECT *
FROM `categories` as c1
LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_name`
LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_name`
where c1.`parent_category_name` IS NULL");echo 'Results is: ' . $result;

answered Jun 12, 2015 at 9:16

Bruce

1,6694 gold badges20 silver badges22 bronze badges

Collectives™ on Stack Overflow

Fetch data from Hierarchical Data in MySQL

Categories Table

company_category_mapping Table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Categories Table

company_category_mapping Table

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related