php - Obtaining categories and subcategories joined from Mysql tables -

i have 3 tables. 1 table called places, table holds different places (such businesses, restaurants, etc...) each "place" linked table called categories, categories have subcategories in hierarchical structure (i.e. thai food subcategory of restaurants). system lets admin create places , matches them corresponding category or subcategory.

what know best way obtain these relations? using joins , group_concat still not getting result set.

these tables:

places:

+----+----------------+ | id | name           | +----+----------------+ | 1  | mings place    | +----+----------------+ | 2  | halsey library | +----+----------------+ | 3  | stellas uso    | +----+----------------+ 

places_categories_rel:

+----------+-------------+ | place_id | category_id | +----------+-------------+ | 1        | 2           | +----------+-------------+ | 2        | 4           | +----------+-------------+ | 3        | 3           | +----------+-------------+ | 3        | 4           | +----------+-------------+ 

categories:

+----+-----------+------------+ | id | parent_id | name       | +----+-----------+------------+ | 1  | null      | restaurant | +----+-----------+------------+ | 2  | 1         | thai food  | +----+-----------+------------+ | 3  | 1         | italian    | +----+-----------+------------+ | 4  | null      | government | +----+-----------+------------+ | 5  | 4         | library    | +----+-----------+------------+ | 6  | 4         | military   | +----+-----------+------------+ 

as can see places attached subcategory, places attached directly parent category (without subcategories), , places have multiple subcategories have different parent categories.

to obtain results, attempted following sql:

'select `p`.*,          group_concat(distinct(concat (ca.id, "|", `ca`.`parent_id`, "|", ca.name))) subcategories,          group_concat(distinct(concat (ca1.id, "|", `ca1`.`parent_id`, "|", ca1.name))) categories `places` `p` left join `places_categories_rel` `rel` on `rel`.`place_id`=`p`.`id` left join `categories` `ca` on `ca`.`id`=`rel`.`category_id` left join `categories` `ca1` on `ca1`.`id`=`ca`.`parent_id` group `p`.`id` 

but results come categories being set subcategories or vice versa.

what have type of result set allow me have this:

array (size=3)   0 =>      array (size=2)       'name' => string 'mings place' (length=11)       'categories' =>          array (size=1)           0 =>              array (size=2)               'name' => string 'restaurant' (length=10)               'subcategories' =>                  array (size=1)                   0 => string 'thai food' (length=9)   1 =>      array (size=2)       'name' => string 'halsey library' (length=14)       'categories' =>          array (size=1)           0 =>              array (size=2)               'name' => string 'government' (length=10)               'subcategories' =>                  array (size=1)                   0 => string 'library' (length=7)   2 =>      array (size=2)       'name' => string 'stellas' (length=7)       'categories' =>          array (size=2)           0 =>              array (size=2)               'name' => string 'restaurant' (length=10)               'subcategories' =>                  array (size=1)                   0 => string 'library' (length=7)           1 =>              array (size=2)               'name' => string 'government' (length=10)               'subcategories' => null 

ideally, created can input filters. example if want see places category of restaurants, or places government , subcategory of italian food...

can please me figure out?

as long have 2 levels of categories (e.g. no sub-subcategories), using join can fine.

the problem is, data references parent categories, , references subcategories. that's why you're getting mixed results.

to avoid mixed results, can use this:

select   p.*,   concat(ifnull(group_concat(c_parent...), ''), ',', ifnull(group_concat(c_direct_parent...), '')),   group_concat(c_child...), `places` p   left join `places_categories_rel` rel     on rel.`place_id` = p.`id`   left join `categories` c_direct_parent     on c_direct_parent.`id` = rel.`category_id`     , c_direct_parent.`parent_id` null   left join `categories` c_child     on c_child.`id` = rel.`category_id`     , c_child.`parent_id` not null   left join `categories` c_parent     on c_parent.`id` = c_child.`parent_id` group p.`id` 

i'm not sure efficient option, can see, need separate out "related categories parents" (c_direct_parent) , "related categories are children" (c_child).

with separation, can retrieve list of data parents , children. clarity, put "..." group_concat internals.