Jump to content

fetch mysql from database and ouput in a html table


bogdaniel

Recommended Posts

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

The image is an example of want i want to achieve but without any luck.

table.png

`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.

Link to comment
Share on other sites

What is the problem here?  You have posted this twice yet you aren't really showing us the problem. 

 

1 - you have data retrieved from your db

2 - why is it a multi-dimension array - your query didn't do that.

3 - simply take the data, row by row using fetch, and write out the appropriate td elements arranged in rows (tr) and there's your table.

4 - What permissions?  Permissions on a table cell?   ??

Link to comment
Share on other sites

yes it retrieves the data but i didn't manage echoing in the table. if you check the html output you would see there that it created a third Catalog-View for moderator instead of putting 1 on the column moderator.

for the number 2

    public function ListPermissionNames() {
        $this->database->query("SELECT t2.RoleName, t2.RoleName, t3.PermissionName, t3.PermissionName, t1.PermissionId 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");
        $this->database->execute();
        $this->database->setFetchMode(PDO::FETCH_ASSOC);
        return $this->database->resultSet(PDO::FETCH_GROUP, PDO::FETCH_ASSOC);

3 & 4 i posted a picture with this post to show exact the example that i wanna achieve and i don't know how to do it. i'm generating td's and everything but instead of putting 1 for administrator and 1 for moderator it creates a new line bellow ( see HTML OUTPUT).

Link to comment
Share on other sites

Here's how, but you will have to do the TWIG bits

<?php
include("db_inc.php");  // defines credentials
$db = new mysqli(HOST,USERNAME,PASSWORD,'bogdaniel');

// GET THE ROLES TO USE AS HEADINGS

$sql = "SELECT RoleId, RoleName
        FROM tbl_user_roles
        ORDER BY RoleId";
$res = $db->query($sql);
$roles = array();
while (list($rid, $rnm) = $res->fetch_row()) {
    $roles[$rid] = $rnm;
}

$theads = "<tr><th>Permission</th><th>".join('</th><th>',$roles)."</th></tr>\n";
$tdata = '';

// CREATE A DEFAULT ARRAY TO STORE THE ROLE VALUES FOR EACH PERMISSION
// THIS WILL BE UPDATED AND OUTPUT WHEN THE PERMISSION CHANGES

$default = array_fill_keys(array_keys($roles), 'x');

// NOW WE CAN GET THE DATA FOR THE TABLE

$sql = "SELECT t3.PermissionName, t2.RoleId 
        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
        ORDER BY t1.PermissionId, t2.RoleId";
$res = $db->query($sql);
$permArray = $default;
$currPerm = '';
while (list($perm, $rid) = $res->fetch_row()) {
    if ($perm != $currPerm) {      // change of permission?
        if ($currPerm != '') {
            $tdata .= "<tr><td class='perm'>$currPerm</td><td>"
                . join ('</td><td>', $permArray) . "</td></tr>\n";
        }
        $currPerm = $perm;         // reset for next permission
        $permArray = $default;
    }
    $permArray[$rid] = '&check;';  // update if perm/role exists
}
// DO NOT FORGET THE FINAL PERMISSION
$tdata .= "<tr><td class='perm'>$currPerm</td><td>"
    . join ('</td><td>', $permArray) . "</td></tr>\n";
?>
<html>
<head>
    <title>Sample</title>
    <meta name="author" content="Barand">
    <meta name="creation-date" content="10/31/2014">
    <style type="text/css">
        table {
            border-collapse: collapse;
        }
        th {
            width: 70px;
            background-color: #ccc;
            font-family: sans-serif;
            font-size: 8pt;
        }
        td {
            font-family: sans-serif;
            font-size: 12pt;
            text-align: center;
        }
        td.perm {
            font-size: 8pt;
            text-align: left;
            width: 150px;
        }
    </style>
</head>
<body>
    <table border='1'>
        <?=$theads?>
        <?=$tdata?>
    </table>
</body>
</html>

Gives:

 

post-3105-0-95674100-1414769760_thumb.png

  • Like 1
Link to comment
Share on other sites

Here's how, but you will have to do the TWIG bits

<?php
include("db_inc.php");  // defines credentials
$db = new mysqli(HOST,USERNAME,PASSWORD,'bogdaniel');

// GET THE ROLES TO USE AS HEADINGS

$sql = "SELECT RoleId, RoleName
        FROM tbl_user_roles
        ORDER BY RoleId";
$res = $db->query($sql);
$roles = array();
while (list($rid, $rnm) = $res->fetch_row()) {
    $roles[$rid] = $rnm;
}

$theads = "<tr><th>Permission</th><th>".join('</th><th>',$roles)."</th></tr>\n";
$tdata = '';

// CREATE A DEFAULT ARRAY TO STORE THE ROLE VALUES FOR EACH PERMISSION
// THIS WILL BE UPDATED AND OUTPUT WHEN THE PERMISSION CHANGES

$default = array_fill_keys(array_keys($roles), 'x');

// NOW WE CAN GET THE DATA FOR THE TABLE

$sql = "SELECT t3.PermissionName, t2.RoleId 
        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
        ORDER BY t1.PermissionId, t2.RoleId";
$res = $db->query($sql);
$permArray = $default;
$currPerm = '';
while (list($perm, $rid) = $res->fetch_row()) {
    if ($perm != $currPerm) {      // change of permission?
        if ($currPerm != '') {
            $tdata .= "<tr><td class='perm'>$currPerm</td><td>"
                . join ('</td><td>', $permArray) . "</td></tr>\n";
        }
        $currPerm = $perm;         // reset for next permission
        $permArray = $default;
    }
    $permArray[$rid] = '&check;';  // update if perm/role exists
}
// DO NOT FORGET THE FINAL PERMISSION
$tdata .= "<tr><td class='perm'>$currPerm</td><td>"
    . join ('</td><td>', $permArray) . "</td></tr>\n";
?>
<html>
<head>
    <title>Sample</title>
    <meta name="author" content="Barand">
    <meta name="creation-date" content="10/31/2014">
    <style type="text/css">
        table {
            border-collapse: collapse;
        }
        th {
            width: 70px;
            background-color: #ccc;
            font-family: sans-serif;
            font-size: 8pt;
        }
        td {
            font-family: sans-serif;
            font-size: 12pt;
            text-align: center;
        }
        td.perm {
            font-size: 8pt;
            text-align: left;
            width: 150px;
        }
    </style>
</head>
<body>
    <table border='1'>
        <?=$theads?>
        <?=$tdata?>
    </table>
</body>
</html>

Gives:

when i get  home i'll test the code no worries for the twig i'll rewrite the code to be with what i'm using i already noticed a difference in the mysql query that from what i can think of might make the difference, i wasn't sure on the sql part if it's done right and complete.. i'm sure i've missed out something about the data output. let know in 3 hours what i've done. cheers for answering to my post

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.