How can I fetch lists within lists (similar to multidimensional arrays) ?

Question

This is a problem I often run into, and my solution has always to run numerous queries, but I feel there must be a better way. Here are my two tables for this example:

Artists
id
name

Songs
id
artistID
name
genre

The table are linked as such:
artists.id <-> songs.artistID

I'd like to fetch a list of all artists and at the same time fetch a list of all songs per artist. I'd like to avoid duplicate artist names in my result set, so that I don't have to clean the data set in PHP. Here's an example of the format I'd like the data to be in:

array(0 => array('name' => 'Artist A'
                  , 'songs' => array(0 => array('name' => 'Song 1'
                                               , 'genre' => 'rock'
                                               )
                                     )
                                     1 => array('name' => 'Song 2'
                                               , 'genre' => 'rock'
                                               )
                                     )
                 )
      1 => array('name' => 'Artist B'
                  , 'songs' => array(0 => array('name' => 'Song 3'
                                               , 'genre' => 'rap'
                                               )
                                     )
                                     1 => array('name' => 'Song 4'
                                               , 'genre' => 'rap'
                                               )
                                     )
                 )
      )

(I know this isn't a MySQL format, but it's the closest way I could explain)

Is this possible? If so, how?

Thanks!

Answer 1

I am afraid it's not possible to do what you want because SQL is inherently a set-based language. You can get all the info that you are looking for in one call via the use of table joins, but after that, you would have to parse them and store them into the structure that you mentioned.

Answer 2

The best thing I can suggest is a query for the parent records followed by a query for the children using an in (parent id list) query. This will give you two data sets. One for the parents and one for the children. On the client side you can then take a parent id and get a set of children records.

Answer 3

Option 1:

1. Query the artists table
2. For each artist:
   1. store the info into an array.
   2. query the songs table and attach the results to the array

PROs:
No unnecessary information being passed from the database.

CONs:
Potentially lots and lots of queries being run.

Option 2:

1. Query both tables using a JOIN (perhaps a left join would be best), order by artist
2. For each row:
   1. Check if this row's artist is the same as the last:
      • if not, store the artist info into the top level of an array
   2. Store the song info into a sub-array of the artist's array

PROs:
Only one query required.

CONs:
Potentially lots and lots of unnecessary data being passed from the db.