Convert mysql result (big associative array) to nested arrays

Question

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 dimensional 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

Please add what the resulting rows look like, will be easier to respond then. A few will suffice. — Stefan H Singer
– Stefan H Singer, Commented Jul 15, 2011 at 9:31

Stefan H Singer · Accepted Answer · 2011-07-15 09:49:54Z

2

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']
  );
}

answered Jul 15, 2011 at 9:49

Stefan H Singer

5,4942 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian · Accepted Answer · 2011-07-15 09:42:09Z

1

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.

answered Jul 15, 2011 at 9:42

Brian

8,6212 gold badges27 silver badges32 bronze badges

1 Comment

Michas Over a year ago

The PostrgreSQL has concept of arrays.

Raffael · Accepted Answer · 2011-07-15 09:49:32Z

1

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.

answered Jul 15, 2011 at 9:49

Raffael

20.1k16 gold badges93 silver badges169 bronze badges

1 Comment

Brian Over a year ago

Good call, forgot about PDO for a moment :)

Collectives™ on Stack Overflow

Convert mysql result (big associative array) to nested arrays

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related