MySQL : GetAncestry

MySQL : GetAncestry

----------GET ANCESTORS MYSQL CUSTOM FUNCTION------

DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$# MySQL returned an empty result set (i.e. zero rows).

CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM category WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$# MySQL returned an empty result set (i.e. zero rows).

DELIMITER ;