Database Trees with MySql

Question

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

• Rick James: CONCAT --> CONCAT_WS

Answer 1

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.

• Mr. Bland: Appreciate the input. I will start looking at that. Do you think there is a better way approach.
• Rick James: It is probably better to avoid the 'hierarchy' whenever practical.
• Mr. Bland: I cannot It is the very core of the application being built.