Question 276774 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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"];
}

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search