select children categories in mysql

Question

I have a nested categories. this is structure.

CREATE TABLE 'category' (
  'id' INT,
  'title' VARCHAR(50),
  'category_id' INT,
)

relation is simple

(id, parent id)

main (1, NULL)
--parent (2, 1)
----child1 (3, 2)
----child2 (4, 2)
--foo (5, 1)
----baz (6, 5)
--bar (7, 1)

I want to select category's childrens (MYSQL)

for "parent" category it is "child1" and "child2"

for "main" category it is:

--parent (2, 1)
----child1 (3, 2)
----child2 (4, 2)
--foo (5, 1)
----baz (6, 5)
--bar (7, 1)

thanks!

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

I think that with this scheme you cannot perform the select with only one request. But this article and this answer may help.

Edit: I've found that article on code projects that looks like your implementation.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 03 '11 at 10:34

Aif

11,015
1
30
44

score 0 · Answer 2 · answered Sep 03 '11 at 10:46

if you have a limit on the number of levels it's no problem, just write down a query that selects children for each level. it will look something like this:

SELECT *....
    FROM category AS level1
    LEFT JOIN category AS level2 ON (c2.category_id = c1.id)
    LEFT JOIN category AS level3 ON (c3.category_id = c2.id)
    ...
WHERE c1.category_id = 0

if you have no limit on the number of children you can:

write a mysql recursive function
fetch all mysql data and make the parent/children matching under the prog. language (ie php)
save somewhere (if you have a config table somewhere) the number of levels yo u have, and implement a query string generator that creates the query string for a given number of levels. on each update/insert you should check if the number of levels is changed and store it

select children categories in mysql

2 Answers2