Joining more than two mysql tables then manipulating rows into grouped multidimensional arrays

Question

I'm trying to join multiple myqsl tables and then process resulting arrays using PHP but I'm having problems manipulating my data to get the groupings I'd like.

table.users
+---------------
uid
name

table.profile_values
+-----------------------
fid
uid
category_value

table.profile_fields
+---------------------
fid
category_title

Here is my sql query:

SELECT users.name, profile_fields.category_title, profile_values.category_value FROM profile_values 
    INNER JOIN profile_fields
        ON profile_values.fid=profile_fields.fid
    INNER JOIN users
        ON users.uid=profile_values.uid
    ORDER BY users.name ASC

Using I while loop over fetch_array(), as expected, I get an array for each row number which looks something like:

Array (
    [0] => Array (
            [name] => Bob
            [category_title] => Occupation 
            [category_value] => IT
            ) 
    [1] => Array ( 
            [name] => Bob 
            [category_title] => Previous Experience 
            [category_value] => Very little.
            ) 
    ...
)

The output I'm actually looking to generate is:

Array( 
    [name] => array(
            [category_title 1] => value 1
            [category_title 2] => value 2
            ...
    )
    ....
)

I've spent the vast part of a day looking at various examples and haven't been able to find one that helps me understand the best place to interject and group my data. I've seen examples using GROUP_CONCAT, which is similar to what I want, but I'd like to keep my data in arrays if possible.

Should I be using a nested foreach loops after I have my rows assigned to arrays, using a GROUP BY in my sql statement, or a combination of both?

Answer 1

Use php to create the array you want then. Maybe something like:

$result = mysql_query($sql) or die(mysql_error());
$newArray = array();
while($row = mysql_fetch_assoc($result)){
    $newArray[$row['name']][$row['category_title']][] = $row['category_value'];
}