Mysql query to find the family head

[bishu]

I was trying to write some custom page and wrote a mysql query to find the family head as

$sql = "DELIMITER $$
                DROP FUNCTION IF EXISTS `GetAncestry` $$
                CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
                DETERMINISTIC
                BEGIN
                    DECLARE rv INT;
                    DECLARE ch INT;
                        SET ch = GivenID;
                    WHILE ch > 0 DO
                        SELECT IFNULL((SELECT `user` FROM fcms_relationship INNER JOIN fcms_users ON fcms_relationship.`user` = fcms_users.id WHERE sex = 'M' AND fcms_relationship.relationship = 'CHIL' AND fcms_relationship.rel_user = ch), -1) INTO ch;
                        IF ch > 0 THEN
                            SET rv = ch;
                        END IF;
                    END WHILE;
                        RETURN rv;
                END $$
                DELIMITER ;

                SELECT DISTINCT GetAncestry(rel_user) AS chieftain FROM fcms_relationship WHERE rel_user = ?";
        $chieftain = $this->fcmsDatabase->getRow($sql, $userId);
        echo $chieftain["chieftain"];

It return FALSE but when I run the same query at mysql server it give the id of the familyhead.

[ryanhowdy]

I think it's because of how the getRow method works. You may need to not use that method to get this sql to work.