Database Merge Results

Hello, I have a php/database related question. I have three database tables (users, perms, lnk_perms_users) where users and perms (permissions) have a many-to-many relationship through lnk_perms_users. After a “complicated” query with some joins, I get the below mysql result. It pulls two users: user 1 has 3 permissions and user 2 has 2 permissions. The result itself is correct.

[CODE]
Array
(
[0] => Array
(
[id] => 1
[email] => [email protected]
[password] => password
[first_name] => User1First
[last_name] => User1Last
[perm_id] => 1
[perm_name] => Perm1
[perm_description] => This is Permission 1
)
[1] => Array
(
[id] => 1
[email] => [email protected]
[password] => password
[first_name] => User1First
[last_name] => User1Last
[perm_id] => 2
[perm_name] => Perm2
[perm_description] => This is Permission 2
)
[2] => Array
(
[id] => 1
[email] => [email protected]
[password] => password
[first_name] => User1First
[last_name] => User1Last
[perm_id] => 3
[perm_name] => Perm3
[perm_description] => This is Permission 3
)
[3] => Array
(
[id] => 2
[email] => [email protected]
[password] => password
[first_name] => User2First
[last_name] => User2Last
[perm_id] => 1
[perm_name] => Perm1
[perm_description] => This is Permission 1
)
[4] => Array
(
[id] => 2
[email] => [email protected]
[password] => password
[first_name] => User2First
[last_name] => User2Last
[perm_id] => 3
[perm_name] => Perm3
[perm_description] => This is Permission 3
)
)
[/CODE]

What I would like is something like this:

[CODE]
Array
(
[0] => Array
(
[id] => 1
[email] => [email protected]
[password] => password
[first_name] => User1First
[last_name] => User1Last
[perms] => Array
(
[0] => Array
(
[perm_id] => 1
[perm_name] => Perm1
[perm_description] => This is Permission 1
)
[1] => Array
(
[perm_id] => 2
[perm_name] => Perm2
[perm_description] => This is Permission 2
)
[2] => Array
(
[perm_id] => 3
[perm_name] => Perm3
[perm_description] => This is Permission 3
)
)

)
[1] => Array
(
[id] => 2
[email] => [email protected]
[password] => password
[first_name] => User2First
[last_name] => User2Last
[perms] => Array
(
[0] => Array
(
[perm_id] => 1
[perm_name] => Perm1
[perm_description] => This is Permission 1
)
[1] => Array
(
[perm_id] => 3
[perm_name] => Perm3
[perm_description] => This is Permission 3
)
)

)
)
[/CODE]

I’m sure this is a common problem with applications that join multiple tables and want to group results. Is there a better query approach to this possibly?

Unfortunately, array_merge, array_merge_recursive, just doesn’t cut it this time…

I have a “dirty” approach to solving this problem, but I would like to see if the community has elegant approaches.

I tried searching the internet/forums but no such luck… It’s very hard to put into words ?.

Any bit of help/direction would be greatly appreciated

Also in #PHP _↴