Jump to content
dishadcruze

Displaying comma delimited array values into checkbox php

Recommended Posts

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

result.PNG.0025c20167bca92447822f9fd00a6381.PNG

But required result is

reqd.PNG.d7b5d6c554179fd513687c76a270968b.PNG

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

getting.PNG.aa705eabe2607d0b17b796b9ad7e3247.PNG

 

How to get the names for that file.

 

 

 

Share this post


Link to post
Share on other sites

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

image.png.43fbb3d592dcf95c9bf0f920501af80a.png

Edited by Barand
  • Thanks 1

Share this post


Link to post
Share on other sites

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       |
+-----+-----------+---------+--------------+---------+

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.