MySql - Get all child categories until not exists

Question

Based on this answer, can you please tell me what's wrong in the next code ? I'm trying to get all child categories.

The example that I try it is:

DELIMITER $$
DROP FUNCTION IF EXISTS `get_category_child_ids` $$
CREATE FUNCTION `get_category_child_ids`(GivenID INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_category_ids VARCHAR(1024);
    DECLARE return_separator CHAR(1);
    DECLARE return_category_id INT;

    SET return_category_ids = '';
    SET return_separator = '';
    SET return_category_id = GivenID;

    WHILE (return_category_id is NOT null AND return_category_id != '') DO
        SELECT IFNULL(category_id,'') INTO return_category_id FROM
        (SELECT category_id FROM category WHERE category_parent_id = return_category_id) A;
        if(return_category_id is NOT null AND return_category_id != '')
        THEN
            SET return_category_ids = CONCAT(return_category_ids,return_separator,return_category_id);
            SET return_separator = ',';
        END IF;
    END WHILE;
    RETURN return_category_ids;
END

But when executing with php, the mysql is freezing...

The

(return_category_id is NOT null AND return_category_id != '')

Should stop the query, but is not.

Answer 1

You are failing to stop when category_parent_id is NULL or zero or whatever signals the top of the tree.