Generating an html table based on data from mysql

PHP
,
1

Hello I have an array with data from mysql that I would like to output it in a table using twig.

What I want to achieve is this - http://postimg.org/image/4us77vyqj/ - but every time I tried getting the same result i end up with the wrong model.

print_r of the array data

   Array
(
    [Administrator] => Array
        (
            [0] => Array
                (
                    [RoleName] => Administrator
                    [PermissionName] => Catalog-View
                    [PermissionId] => 1
                )

            [1] => Array
                (
                    [RoleName] => Administrator
                    [PermissionName] => Catalog-Edit
                    [PermissionId] => 2
                )

            [2] => Array
                (
                    [RoleName] => Administrator
                    [PermissionName] => Catalog-Delete
                    [PermissionId] => 3
                )

        )

    [Moderator] => Array
        (
            [0] => Array
                (
                    [RoleName] => Moderator
                    [PermissionName] => Catalog-View
                    [PermissionId] => 1
                )

        )

)

The HTML code:

<table>
    <tr>
        <thead>
            <th>Controller - Action</th>
            {% for permission in permissions %}
            {% for item in permission %}
            <th>{{item.RoleName}}</th>
            {% endfor %}
            {% endfor %}
        </thead>
    </tr>
    {% for permission in permissions %}
    {% for item in permission %}
    <tr>
        <td>{{item.PermissionName}}</td>
        <td>{{item.PermissionId}}</td>
    </tr>
    {% endfor %}
    {% endfor %}
</table>

OUTPUT:

<table>
    <tbody>
        <tr></tr>
    </tbody>
    <thead>
        <tr>
            <th>Controller - Action</th>
            <th>Administrator</th>
            <th>Administrator</th>
            <th>Administrator</th>
            <th>Moderator</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Catalog-View</td>
            <td>1</td>
        </tr>
        <tr>
            <td>Catalog-Edit</td>
            <td>2</td>
        </tr>
        <tr>
            <td>Catalog-Delete</td>
            <td>3</td>
        </tr>
        <tr>
            <td>Catalog-View</td>
            <td>1</td>
        </tr>
    </tbody>
</table>

Later Edit
MySQL Query:

SELECT t3.PermissionName, t1.PermissionId, t2.RoleName FROM tbl_user_role_perm AS t1
INNER JOIN tbl_user_roles AS t2 ON t1.RoleId = t2.RoleId
INNER JOIN tbl_user_permissions AS t3 ON t1.PermissionId = t3.PermissionId

MySQL Dump:

-- Dumping structure for table tbl_user_permissions
CREATE TABLE IF NOT EXISTS `tbl_user_permissions` (
  `PermissionId` int(11) NOT NULL AUTO_INCREMENT,
  `PermissionName` varchar(50) NOT NULL,
  `PermissionDescription` varchar(100) NOT NULL,
  PRIMARY KEY (`PermissionId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- Dumping data for table tbl_user_permissions: ~2 rows (approximately)
DELETE FROM `tbl_user_permissions`;
/*!40000 ALTER TABLE `tbl_user_permissions` DISABLE KEYS */;
INSERT INTO `tbl_user_permissions` (`PermissionId`, `PermissionName`, `PermissionDescription`) VALUES
    (1, 'Catalog->View', 'View Catalog Method'),
    (2, 'Catalog->Edit', 'Edit Catalog Method'),
    (3, 'Catalog->Delete', 'Delete Catalog Method');
/*!40000 ALTER TABLE `tbl_user_permissions` ENABLE KEYS */;


-- Dumping structure for table tbl_user_role
CREATE TABLE IF NOT EXISTS `tbl_user_role` (
  `UserRoleId` int(10) NOT NULL AUTO_INCREMENT,
  `UserId` int(10) NOT NULL,
  `RoleId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`UserRoleId`),
  KEY `FK_tbl_user_role_tbl_user_roles` (`RoleId`),
  KEY `UserId` (`UserId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- Dumping data for table tbl_user_role: ~2 rows (approximately)
DELETE FROM `tbl_user_role`;
/*!40000 ALTER TABLE `tbl_user_role` DISABLE KEYS */;
INSERT INTO `tbl_user_role` (`UserRoleId`, `UserId`, `RoleId`) VALUES
    (1, 13, 22),
    (2, 14, 22);
/*!40000 ALTER TABLE `tbl_user_role` ENABLE KEYS */;


-- Dumping structure for table tbl_user_roles
CREATE TABLE IF NOT EXISTS `tbl_user_roles` (
  `RoleId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `RoleName` varchar(50) NOT NULL,
  `CreatedDate` datetime NOT NULL,
  `ModifiedDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`RoleId`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

-- Dumping data for table tbl_user_roles: ~7 rows (approximately)
DELETE FROM `tbl_user_roles`;
/*!40000 ALTER TABLE `tbl_user_roles` DISABLE KEYS */;
INSERT INTO `tbl_user_roles` (`RoleId`, `RoleName`, `CreatedDate`, `ModifiedDate`) VALUES
    (22, 'Administrator', '2014-10-28 09:53:08', NULL),
    (23, 'Moderator', '2014-10-28 09:53:13', NULL),
    (24, 'Admin', '2014-10-28 12:22:05', '2014-10-28 12:22:06'),
    (25, 'User', '2014-10-29 15:10:36', '2014-10-29 15:10:37'),
    (26, 'SuperUser', '2014-10-29 15:10:45', '2014-10-29 15:10:46'),
    (27, 'Accountant', '2014-10-29 15:10:53', '2014-10-29 15:10:54'),
    (28, 'God', '2014-10-29 15:11:02', '2014-10-29 15:11:02');
/*!40000 ALTER TABLE `tbl_user_roles` ENABLE KEYS */;


-- Dumping structure for table tbl_user_role_perm
CREATE TABLE IF NOT EXISTS `tbl_user_role_perm` (
  `RoleId` int(10) unsigned NOT NULL,
  `PermissionId` int(10) unsigned NOT NULL,
  KEY `RoleId` (`RoleId`),
  KEY `PermissionId` (`PermissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table tbl_user_role_perm: ~3 rows (approximately)
DELETE FROM `tbl_user_role_perm`;
/*!40000 ALTER TABLE `tbl_user_role_perm` DISABLE KEYS */;
INSERT INTO `tbl_user_role_perm` (`RoleId`, `PermissionId`) VALUES
    (22, 2),
    (22, 1),
    (23, 1),
    (22, 3);
/*!40000 ALTER TABLE `tbl_user_role_perm` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Can you help me to make the coding required so that the correct column have the correct permissions? thank you in advance.

2

Hi!

First, for readability, shouldn’t it be:

        {% for role in roles %}
        {% for permission in role %}

instead of

        {% for permission in permissions %}
        {% for item in permission %}

?

Keep in mind that it’s late here, so maybe there’s a simpler solution, but I think what you need is an array of permissions with the roles after. You have the array of roles to make the columns. Now you need to make the rows with the permissions.

It would be easier if you had an array like:

Edit
Administrator => false
Moderator => true
Delete
Administrato => true
Moderator => true
…

What you have now is a partial array (like, for the moderator, he has only one entry)… To represent a table with an array, you need an array that represents the table 1 for 1. You can do that with SQL or code (you could create a new array and loop on the one you have to generate the new array), but when you’re into your template, you only want to loop and not add conditions here and there. And, with the array you have now, I don’t think it’s feasible.

3

I can’t go into detail right now but I would create a result set as explained by xMog using two separate queries. The first query would gather all the roles. Than the second query would be constructed dynamically with case statements where 0 or 1 exists and each column is a role and each row a permission. That way you have an exact match of what you need to display. You could get away doing it with a single query but there would be a lot of data messaging to do. I think using two separate queries in order to create columns for each role is the simplest approach. If someone doesn’t show you how to do that if I have time later I’ll go into further detail.

4

Something like this would work. I didn’t test the SQL so treat it as pseudo-code. A minimal amount of massaging would be necessary after-wards to create the table. There are two group concats there so that the 0/1 can be mapped to the proper role for each permission. The order by portion of the group_concat function guarantees a 1:1 relation with indexes so item 2 in perms belongs to role 2 in perms_order.

Edit: I actually missed something here. Need to join against all roles, oops. Will correct when I have time.

SELECT
     p.PermissionId
     ,GROUP_CONCAT(CASE WHEN r2p.RoleId IS NOT NULL THEN 1 ELSE 0 END; ORDER BY r.RoleId) perms
     ,GROUP_CONCAT(r.RoleId ORDER BY r.RoleId) perms_order
  FROM
     tbl_user_permissions p
  LEFT OUTER
  JOIN
     tbl_user_role_perm r2p
    ON
     p.PermissionId = r2p.PermissionId
  LEFT OUTER
  JOIN
     tbl_user_roles r
    ON
     r2p.RoleId = r.RoleId
 GROUP
    BY
     p.PermissionId
5

I would use the below query constructing the case portion on the application end. This way you have everything you need to easily present the data how you want without very much massaging after the query takes place.

SELECT
     p.PermissionId
     ,p.PermissionName
     
     ,CASE
        WHEN FIND_IN_SET('22',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_22
     
     ,CASE
        WHEN FIND_IN_SET('23',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_23
     
     ,CASE
        WHEN FIND_IN_SET('24',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_24
     
     ,CASE
        WHEN FIND_IN_SET('25',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_25
     
     ,CASE
        WHEN FIND_IN_SET('26',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_26
     
     ,CASE
        WHEN FIND_IN_SET('27',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_27
     
     ,CASE
        WHEN FIND_IN_SET('28',GROUP_CONCAT(r2p.RoleId SEPARATOR ',')) <> 0
        THEN 1
        ELSE 0 
     END role_28
     
  FROM
     tbl_user_permissions p
  LEFT OUTER
  JOIN
     tbl_user_role_perm r2p
    ON
     p.PermissionId = r2p.PermissionId
 GROUP
    BY
     p.PermissionId;
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.