Break loop when creating nested groups in php
I have a functionality in my project where we create groups and child groups. But here the child group has high priority from the parent.
eg. A (parent) -> B(child) -> C(sub child)
B is associated with A
C is associated with B
In the next step, I can link it with A. But it will create a loop that I need to break. Here above C has the highest priority, can also access B (data) and A (data), and B with second priority and can access with A (data).
My Db structure:
Group table
------------------------------------------
id name parentId
------------------------------------------
1 A null
2 B 1
3 C 2
-------------------------------------------
Group_Data Table
--------------------------------------
gd_id grp_id Data
--------------------------------------
11 1 Peter
22 2 Dennis
33 3 Jene
----------------------------------------
So when I access and try to map group A to parent as C then that shouldn't let me in. I want to write a sql query for this. Please help me with this.
Thank!
+3
source to share
1 answer
You can create the following mysql function to get all ancestors
DELIMITER $$
DROP FUNCTION IF EXISTS `GetGroupAncestry` $$
CREATE FUNCTION `GetGroupAncestry` (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(parentId,-1) INTO ch FROM
(SELECT parentId FROM Group WHERE id = ch) A;
IF ch > 0 THEN
SET rv = CONCAT(rv,cm,ch);
SET cm = ',';
END IF;
END WHILE;
RETURN rv;
END $$
DELIMITER ;
Using this, you can set a condition in your language on the server side as well as in a query using it as a derived table.
+3
source to share