CODEWITHSUNDEEP
phpmysqlconcatenationgroup-concat
BZohar
BZohar

Reputation: 33

How to get mysql Group_Concat to work with additional CONCAT information?

I'm working on a web app with mysql involving a user role table where permission levels are stored as well as role ids and associated to usernames and their ids (redundancy to continue supporting legacy code).

I want to be able to display every user's role as a comma seperated list in plain English with minimal hit to the DB (>1k users) as well as their permission level.

Permissions are stored as bits in the ismanager and ishead columns and roleids are keyed to define_roles (id, rolename)

Here's what I've got works (but doesn't show user levels)

<?
$rolenames = array();
foreach($db->query("SELECT id, rolename FROM define_roles") as $row)
{
    $rolenames[$row['id']] = $row['rolename'];
}

foreach($db->query("SELECT DISTINCT userid, username, 
                        GROUP_CONCAT(DISTINCT roleid  ORDER BY roleid) AS idlist
                        FROM user_roles 
                        GROUP BY userid 
                        ORDER BY username ASC") as $row)
{
    $rolestring = '';
    //echo $row['idlist'];
    foreach(explode(',',$row['idlist']) as $id)
    {
        $rolestring .= " ".$rolenames[$id].",";
    }
    $rolestring = rtrim($rolestring,',');

    echo "<tbody>
            <tr>
                <td><font size='1'>" . $row['username'] . "</font></td>
                <td><font size='1'>" .$rolestring. "</font></td>
            </tr>";
}
?>

Output:

What I want to see is

The best I could come up with (though it doesn't work) is:

$rolenames = array();
foreach($db->query("SELECT id, rolename FROM define_roles") as $row)
{
    $rolenames[$row['id']] = $row['rolename'];
    $rolenames[$row['id']."10"] = $row['rolename']." (Manager)";
    $rolenames[$row['id']."01"] = $row['rolename']." (Head)";
    $rolenames[$row['id']."11"] = $row['rolename']." (Head)";
}

foreach($db->query("SELECT DISTINCT userid, username, 
                        GROUP_CONCAT
                        (
                            CONCAT(roleid,ismanager,ishead) ORDER BY roleid
                        ) AS idlist
                        FROM user_roles 
                        GROUP BY userid 
                        ORDER BY username ASC") as $row)

Upvotes: 3

Views: 563

Answers (1)

Matt Parker
Matt Parker

Reputation: 284

Firstly, you should probably normalize your database so that you have a separate table linking the users and defined roles. But that's a separate thing and I appreciate you may not be able to do that.

If I've understood what's in your tables correctly, the answer is this, I think:

SELECT 
    userid, 
    username, 
    GROUP_CONCAT(DISTINCT 
        CONCAT(
            (SELECT d.rolename FROM define_roles AS d
            WHERE d.id = roleid)
        ), 
        IF(ismanager = 1, " (Manager)", ""),
        IF(ishead = 1, " (Head)", "")
    ) AS roles
FROM user_roles
GROUP BY userid;

You won't need the initial sql where you looked up the rolenames from define_roles.

[EDIT]: Sorry, updated with brackets around the inner SELECT.

Upvotes: 2

Related Questions