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.