0

I am trying to give role-based access in my script. Here is the example of my code and tables

I want to display the pages which the user has access in a checkbox format. I tried to like this

$sql = "SELECT p.page_id AS pid, 
               p.page, 
               p.link, 
               ra.pages AS rpage 
        FROM pages p 
        INNER JOIN role_access ra 
        WHERE p.page_id IN (ra.pages) AND ra.role=1";

$query = mysqli_query($con, $sql) or die(mysqli_error($con));

$checked_arr = array();
while($row = mysqli_fetch_array($query)) { 
$checked_arr = explode(",",$row['rpage']);

foreach ($checked_arr as $page) {

    echo "<br/><input type='checkbox' name=\"pages[]\" value='$page' />$page<br>";
}

for var_dump($row['page']) displays like this string 'New' (length=3) only one page is is getting displayed.

It gives me checkbox with page_id but I want to display p.page value in-front of each checkbox. how to do it?

Here is my table structure.

Pages table

 page_id     |  page    |  link
    1        |  New     |  new.php
    2        |  Edit    |  edit.php
    3        |  Details |  details.php
    4        |  Report  |  export.php

role table

role_id  | role_name
1        | Admin
2        | User1

role_access table

id  |   pages    | role
1   |   1,2,3,4  | 1
2   |   3,4      | 2
Nishal K.R
  • 1,090
  • 11
  • 21
user2178637
  • 117
  • 1
  • 14
  • ra.pages contains ids (1,2,3,4), so IN works for this – user2178637 Sep 25 '19 at 16:59
  • 3
    Do not save the page ids in a comma separated list, see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Progman Sep 25 '19 at 19:35
  • Please read: https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die – Dharman Sep 26 '19 at 16:46

1 Answers1

0

I have normalized the role_access table like this

rid  |  page_id  | role
1    |  1        | 1
2    |  2        | 1
3    |  3        | 1
4    |  4        | 1
5    |  1        | 2
6    |  3        | 2

and got the output as i wanted

user2178637
  • 117
  • 1
  • 14