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:

DROP FUNCTION IF EXISTS `get_category_child_ids` $$
CREATE FUNCTION `get_category_child_ids`(GivenID INT) RETURNS varchar(1024) CHARSET utf8
    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 != '')
            SET return_category_ids = CONCAT(return_category_ids,return_separator,return_category_id);
            SET return_separator = ',';
        END IF;
    RETURN return_category_ids;

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


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

Should stop the query, but is not.

  • 1
    Please provide SHOW CREATE TABLE category; – Rick James Apr 10 '15 at 0:19

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.