Return column alias as associative array key

Question

Say I have the following:

$query = "
    SELECT user_id,email,password, roles.role_id,role_name
    FROM users
    JOIN roles USING(role_id)
    LIMIT 1
    ";
$result = mysql_query($query);
$user = mysql_fetch_assoc($result);

Then if I print_r($user) I get:

Array(
    [user_id] => 1 
    [email] => [email protected]
    [password] => 5f4dcc3b5aa765d61d8327deb882cf99
    [role_id] => 1 
    [role_name] => admin 
    )

I would like to clean this array up a bit by using aliases for my column names in the select query. I can replace user_id with id easily enough but I would also like to choose aliases for role_id and role_name such that they are returned as role => array (id => 1, name => admin).

For example, I have tried the following (which doesn't work):

$query = "
    SELECT user_id AS id,email,password, roles.role_id AS role[id],role_name AS role[name]
    FROM users
    JOIN roles USING(role_id)
    LIMIT 1
    ";

The array I would like this to return would look like:

Array(
    [id] => 1 
    [email] => [email protected]
    [password] => 5f4dcc3b5aa765d61d8327deb882cf99
    [role] => Array(
        [id] => 1
        [name] => admin
        )
    )

Answer 1

You can't do this with mysql_fetch_assoc() alone.

If you really need a multi-dimensional array, you have to do it manually. Possibly create a helper function that loops over a retrieved record, and consolidates multiple keys with the same prefix (in your case, "role_").

Many existing database libraries will do this work for you. For example, CakePHP's database helper would return an array like

Array(
 [User] => array('id' => 1, 'email' => '...'),
 [Role] => array('id' => 1, ...), 
)

Answer 2

The standard mysql_fetch_assoc method won't do this for you: you'll have to do some post-production yourself.

function my_assoc($result) {
    $row = mysql_fetch_assoc($result);
    if (is_array($row)) {
        $out = array();
        foreach ($row as $field => $value) {
            if (preg_match('/^([^\]]+)\[([^\]]+)\]$/', $field, $matches) {
                $out[$matches[1]][$matches[2]] = $value;
            } else {
                $out[$field] = $value;
            }
        }
        return $out;
    } else {
        return $row;
    }
}

This only works (if it works at all -- untested) with a single level of indentation (that is, foo[bar][baz] won't be handled).