Displaying comma delimited array values into checkbox php

[dishadcruze]

I am trying to give role based access here. I want to display the pages which user has access in checkbox format. Here is my code

$sql = "SELECT p.page_id AS pid, 
                            p.page, 
                            p.href, 
                            ra.pages AS rpage 
             FROM pages p 
             INNER JOIN role_access ra 
             WHERE p.page_id IN (ra.page) 
             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>";
}

My tables are like this

ROLE

CREATE TABLE `role` (
  `rid` int(5) NOT NULL,
  `role_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `role` (`rid`, `role_name`) VALUES
(1, 'Admin'),
(2, 'Others');

ALTER TABLE `role`
  ADD PRIMARY KEY (`rid`);


ROLE-ACCESS

CREATE TABLE `role_access` (
  `id` int(10) NOT NULL,
  `page` varchar(160) NOT NULL,
  `role` int(7) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `role_access` (`id`, `page`, `role`) VALUES
(1, '1,2,3,4,5', 1),
(2, '2,4,5', 2);

ALTER TABLE `role_access`
  ADD PRIMARY KEY (`id`);



PAGES

CREATE TABLE `pages` (
  `page_id` int(11) NOT NULL,
  `code` varchar(10) NOT NULL,
  `page` varchar(100) NOT NULL,
  `href` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `pages` (`page_id`, `code`, `page`, `href`) VALUES
(1, 'Patient', 'Registration', '/patient_registration.php'),
(2, 'Patient', 'List', '/patient_list.php'),
(3, 'Patient', 'Edit', '/edit_patient.php'),
(4, 'Patient', 'Export', '/export_patient.php'),
(5, 'Patient', 'MRD', '/patient_MRD.php');

ALTER TABLE `pages`
  ADD PRIMARY KEY (`page_id`);

 

 

In above query i get result like this

But required result is

if i change checkbox display like

while($row = mysqli_fetch_array($query)) { 
$checked_arr = explode(",",$row['rpage']);
$pname = $row['page'];
foreach ($checked_arr as $page) {

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

i get result

 

How to get the names for that file.

 

 

 

[Barand]

The answer is "normalize".

Don't store comma-separated lists (especially when the list items are ids). The role_access table should be

CREATE TABLE `role_access` (
  `id` int(10) NOT NULL PRIMARY KEY,
  `page` int NOT NULL,
  `role` int(7) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `role_access` (`id`, `page`, `role`) VALUES
(1,1,1),
(2,2,1),
(3,3,1),
(4,4,1),
(5,5,1),
(6,2,2),
(7,4,2),
(8,5,2);

Now you can join to the page table to get the page name

Edited September 26, 2019 by Barand

[dishadcruze]

oh! Okay. Thank you!. Thank you @Barand

[Barand]

Sample query I forgot to add to my last post...

SELECT r.rid
     , r.role_name
     , p.page_id
     , p.page
     , CASE WHEN ra.page IS NULL THEN ''
            ELSE 'Y'
       END as checked
FROM role r 
        CROSS JOIN
     pages p 
        LEFT JOIN 
     role_access ra ON r.rid = ra.role
                    AND p.page_id = ra.page
ORDER BY r.rid, p.page_id;

+-----+-----------+---------+--------------+---------+
| rid | role_name | page_id | page         | checked |
+-----+-----------+---------+--------------+---------+
|   1 | Admin     |       1 | Registration | Y       |
|   1 | Admin     |       2 | List         | Y       |
|   1 | Admin     |       3 | Edit         | Y       |
|   1 | Admin     |       4 | Export       | Y       |
|   1 | Admin     |       5 | MRD          | Y       |
|   2 | Others    |       1 | Registration |         |
|   2 | Others    |       2 | List         | Y       |
|   2 | Others    |       3 | Edit         |         |
|   2 | Others    |       4 | Export       | Y       |
|   2 | Others    |       5 | MRD          | Y       |
+-----+-----------+---------+--------------+---------+