Mysql – Database Trees with MySql

MySQL

I have a table of "admins" and what they control "units" each unit and admin are from a different table. I wanted to check if there is a connection between an admin and a unit. The number of levels down is unknown. This is my attempt so far. May I get some help?

DROP FUNCTION IF EXISTS `isadmin`;

DELIMITER //

CREATE FUNCTION isadmin(a_id INT(11), a_table VARCHAR(25), u_id INT(11), u_table VARCHAR(25))
RETURNS INT DETERMINISTIC
BEGIN
DECLARE rtn INT(1);

SET @a_id := a_id;
SET @a_table := a_table;
SET `rtn` := 0;

WHILE @a_id != '' AND @a_table != '' DO
        SELECT `unit_id`,`unit_table`
    INTO @u_id, @u_table FROM `admin` 
    WHERE (`admin_id`,`admin_table`) IN (@a_id, @a_table);
        IF @u_id = `u_id` AND @u_table = `u_table` THEN
                SET `rtn` := 1;
            SET @a := '';
        ELSE
                SET `rtn` := 0;
            SET @a_id := @u_id;
            SET @a_table := @u_table;
        END IF;
END WHILE;

RETURN `rtn`;

END//
DELIMITER ;

SELECT isadmin("1","user","2","group");

Best Answer

I don't think this syntax will work:

IN (`a`)

Instead, you probably need to use CONCAT, etc to construct the SELECT, then PREPARE, EXECUTE, and DEALLOCATE it.

But, I suggest you back up -- Try the SELECTs by hand until you have some confidence that they will deliver the desired results. Only then try to turn it into a Stored Procedure.

Related Question