Convert mysql result (big associative array) to nested arrays

I have a simple query like this

SELECT hometeam.name AS hometeamName, hometeam.shortname AS hometeamShortName, 
       roadteam.name AS roadteamName, roadteam.shortname AS roadteamShortName, 
       smatch.startdate

FROM smatch 
    JOIN team hometeam
        ON smatch.hometeamid = hometeam.uid
    JOIN team roadteam
        ON smatch.roadteamid = roadteam.uid

which be default returns a one dimensiona开发者_开发问答l array, like this:

array(5) {
     ["homeTeamName"] => "Brasil"
     ["homeTeamShortName"] => "BRA"
     ["roadTeamName"] => "Norway"
     ["roadTeamShortName"]=> "NOR"
     ["startdate"]=> "1309709700"
}

Question is, is there a mysql-way to let the result be a nested array, with a structure like the following?

result => 
    hometeam =>
        name
        shortname
    roadteam =>
        name
        shortname
    startdate

And if not, is there a (php) post processing best practice to do this conversion?

Many thanks,

Robson

I don't think there's a way to directly get the result you want, but to generate that array after the query shouldn't be hard.

Something like this?

foreach($rows as $row) {
  $resultArray[] = array(
    'hometeam' => array(
      'name' => $row['homeTeamName'], 
      'shortname' => $row['homeTeamShortName']
      ),
    'roadteam' => array(
      'name' => $row['roadTeamName'], 
      'shortname' => $row['roadTeamShortName']
      ),
    'startdate' => $row['startdate']
  );
}

MySQL (or any database) has no concept of arrays.

The only way I can think of is that you'd need to do it in a Stored Procedure and serialise the result in some way - essentially creating a serialised string... wich you'd then need to re-construct the array from.

However, would be better doing this in PHP to be honest, the gain from doing it in an SP will be minimal. Will be more efficient to query the main and then query within using loops PHP-side.

You have to fetch the result in PDO::FETCH_CLASS mode.

by specifying the __set()-method respectively you can store the result-field-data how ever you like.

This class should also extend ArrayAccess so you can access it like an array or have it return one.