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

Question

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:

TestUser Sales
UserTest2 Sales, Mailroom, Janitorial

What I want to see is

TestUser Sales(Manager)
UserTest2 Sales, Mailroom (Head), Janitorial(Manager)

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)

Matt Parker · Accepted Answer · 2015-01-30T17:15:54.943

2

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.

edited Jan 30 '15 at 17:15

answered Jan 30 '15 at 17:00

Matt Parker

284
1
3

Thank you! Have a slight hiccup, but this does answer the question if I remove the SELECT inside the CONCAT so off the bat so I marked as answer. I'm still getting an error though (phpmyadmin doesn't exactly give useful error reports) "near SELECT d.rolename FROM define_roles AS d WHERE d.id = roleid" I checked in case I mistakenly typo'd a column or table name but it doesn't seem to be the case. I was thinking I might have to define the user_roles table as r and do r.roleid but that didn't do it. – BZohar Jan 30 '15 at 17:10

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

1 Answers1