Jump to content

Archived

This topic is now archived and is closed to further replies.

bogdaniel

fetch mysql from database and ouput in a html table

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.

Share this post


Link to post
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?   ??

Share this post


Link to post
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).

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×
×
  • 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.