Infinite level of process
The subordinate member relationship is determined by ID and PID.
For example: A>b>c>d>e>f>g ...
is the subordinate of A is b,b subordinate is the subordinate of C,c is D .... (a bit similar to the * pin mode?) Ignore)
requirements, through the user G to identify all the superiors, you have to find F.E.D.C.B.A these user IDs
To send notifications to these superiors.
The most important thing for us is to take out our superior ID and then we can do more.
A few values are described first, user_id the user id,parent_id in the user table as the ancestor ID of the user in the table.
Example a basic query parent idsql.
SELECT parent_id from cx_user WHERE user_id = 10;# #查询user_id为10的上级id
Don't talk nonsense, go directly to the center.
Open MySQL to start writing stored procedures.
DROP PROCEDURE IF EXISTS ' getpid '; CREATE definer = ' root ' @ ' localhost ' PROCEDURE ' getpid ' (in ' id ' int) beginset @pid: =id; #id为查询的参数 that is the current user IDSet @pidarr: = '; #查询到的上级数组字符串recursion: While @pid!=0 does SELECT parent_id into @pid from cx_users WHERE user_id = @pid; #这里是你查询的SQL语句 @pi D is the dynamic ID IF @pid =0 then #到0即结束 LEAVE recursion; END IF; IF LOCATE (@pid, @pidarr) >0 then #防止出现上下级关系混乱 LEAVE recursion; END IF; Set @pidarr = Concat (@pidarr, @pid); Set @pidarr = Concat (@pidarr, ', '); END while recursion; SELECT @pidarr as Pidarr; END;
The stored procedure executes the result and takes a picture to scare you.
PHP Side Demo
$sql = "Call Getpid (104);"; $pidstr = mysql_query ($sql);//Change the value of the Pidarr to $pidstr$pidstr = RTrim ($pidstr, ', ') by replacing the MySQL function in your project and/or remove the rightmost superfluous comma $ Pidarr = Explode (', ', $pidstr);//to the array//following the random #ncSJI $ @CO3N #@329090* (@@ () $u) @j (@ (H (@jn9NCD9823N892