List of all children categories for parent category

Question

This function currently gets a child category one level down from the parent. We know the parent ($category_id), it checks for the child category and returns the result correctly.

// list category for the category (type)
function getCategoryItemsList($category_id) {

$cat_list = $category_id;

// check for child category
 $sql = "select category_id from category where category_parent_id = $category_id "; 
 $cat_ids = getSqlResult($sql, SQL_RESULT_ALL);

 foreach($cat_ids as $item) {
$cat_list = $cat_list . ',' . $item['category_id'];
 }
 return $cat_list;
}

The issue is when we have multiple category levels like:

parent > child > grand-child > great-grand-child

How can I return all child category levels of a parent?

///// MYSQL CATEGORY DB
category_id    category_name          category_parent_id
 1             shoes                  0
 2             blue shoes             1
 3             men blue shoes         2
 4             men blue shoe small    3
 5             red shoes              1
 6             men red shoes          5
 7             shirts                 0  

Expected result when $category_id='1' would be 2,3,4,5,6

Answer 1

The best (maybe only) way I know of, if you need to retrieve all parents or children in a table with a tree-like parent/child structure of an arbitrary height, is with a recursive query. It can be a little hard to grasp at first, but it's great exercise and kind of fascinating once you get it to work. There's explanation and example here: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

EDIT: There may be something conceptually wrong with what you're asking. I we assume that a category can have more than one child, the query cannot "know" which child to select and thus will select all of them. In your example, there is only one child pr. category and in this case the query works, but you should keep this assumption in mind. It is fairly easy, though, to just select out the parent on each category in the query below. This is my suggestion:

BEGIN
WITH ct
AS (
    SELECT c1.Id,
        0 Step,
        c1.ParentCategoryId
    FROM _Category c1
    WHERE c1.Id = 132

    UNION ALL

    SELECT c2.Id,
        ct.Step + 1,
        c2.ParentCategoryId
    FROM _Category c2
    JOIN ct
        ON c2.ParentCategoryId = ct.Id
    )
SELECT ct.Id,
    ct.Step
FROM ct;
END

Answer 2

Try this:

create table category(
  id        int,
  name      varchar(100),
  parent_id int
);

insert into category values
  (1, 'shoes', 0),
  (2, 'blue shoes', 1),
  (3, 'men blue shoes', 2),
  (4, 'men blue shoe small', 3),
  (5, 'red shoes', 1),
  (6, 'men red shoes', 5),
  (7, 'shirts', 0);

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM category WHERE
FIND_IN_SET(parent_id, @pv)) AS lv FROM category
JOIN (SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)) a;

Output:

mysql> SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
    -> SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM category WHERE
    -> FIND_IN_SET(parent_id, @pv)) AS lv FROM category
    -> JOIN (SELECT @pv:=1)tmp
    -> WHERE parent_id IN (@pv)) a;
+--------------------------------+
| GROUP_CONCAT(lv SEPARATOR ',') |
+--------------------------------+
| 2,5,3,6,4                      |
+--------------------------------+
1 row in set (0.00 sec)

SQLFiddle demo

This solution is based on that answer