fetch mysql from database and ouput in a html table

bogdaniel · October 31, 2014

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.

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

ginerjm · October 31, 2014

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

bogdaniel · October 31, 2014

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

Barand · October 31, 2014

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:

bogdaniel · October 31, 2014

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

bogdaniel · October 31, 2014

haha i'm soo happy can't wait to get home and check it out

BogdanOlteanu · November 2, 2014

works smooth man thank you very much for your help :-)

Sign In

fetch mysql from database and ouput in a html table

Recommended Posts

bogdaniel

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

bogdaniel

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

bogdaniel

Link to comment

Share on other sites

bogdaniel

Link to comment

Share on other sites

BogdanOlteanu

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information