How to retrieve stored data by delimiter ( ; ) in MySQL-CodePudding

I have 2 Tables Like these:

region Table:
 ---------- ------------- 
|region_id | region_name |
 ---------- ------------- 
|1         | RN1         |
|2         | RN2         |
|3         | RN3         |
 ---------- -------------

and

role Table:
 ---------- ------------- 
|role_id   | role_name   |
 ---------- ------------- 
|1         | admin       |
|2         | software    |
|3         | network     |
 ---------- -------------

In my HTML form I get region_id and role_id from Administrator and store using PHP in MySQL table like this:

useraccess Table:
 -------- ----------- 
|role_id | region_id |
 -------- ----------- 
|1       | 1;2;3     |
|2       | 1         |
|3       | 1;2       |
 -------- -----------

Well, the problem is I don't know how to write a query that can retrieve region_name(s) from region table using useraccess table. for example: when role_id = 1 then region_id is 1;2;3 so instead of 1,2,3 I want to show RN1,RN2,RN3 in my HTML page.

I tried to use for-loop using PHP but it has own limitation and doesn't work correctly. also I used MySQL SPLIT_STR() function but maybe I can't get the result. I will appreciate help me to solve this problem.

CodePudding user response：

you can use this query:

SELECT ua.role_id,
  GROUP_CONCAT(r.region_name SEPARATOR ',') AS region_names
  FROM useraccess ua
  JOIN region r ON FIND_IN_SET(r.region_id, replace(ua.region_id,";",","))
  GROUP BY ua.role_id;

CodePudding user response：

This instruction works better:

SELECT  a.role_id,
    GROUP_CONCAT(b.region_name ORDER BY b.region_id) RegionAccessName
    FROM    useraccess a
    INNER JOIN region b
    ON FIND_IN_SET(b.region_id, a.region_id) > 0
    GROUP   BY a.role_id

but instead of return:

RN1,RN2,RN3
RN1
RN1,RN

Return:

RN1,RN2,RN3
RN1,RN2,RN3
RN1,RN2,RN3

In PHP Code I use this:

while($row2 = mysqli_fetch_array($result2)){;
    echo $row2["RegionAccessName"];
}