0

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");

1
  • CONCAT --> CONCAT_WS
    – Rick James
    Mar 9, 2016 at 20:29

1 Answer 1

0

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.

3
  • Appreciate the input. I will start looking at that. Do you think there is a better way approach.
    – Mr. Bland
    Mar 9, 2016 at 6:33
  • It is probably better to avoid the 'hierarchy' whenever practical.
    – Rick James
    Mar 9, 2016 at 20:30
  • I cannot It is the very core of the application being built.
    – Mr. Bland
    Mar 9, 2016 at 22:57

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.