title: How can I modify this MySQL query to get a particular data structure?
body: <p>If I have two simple tables:</p>
  
  <p>Photos : </p>
  
  <pre><code>photoid | filename            | albumid
  1       | something.jpg       |    1
  2       | somethingelse.jpg   |    1
  </code></pre>
  
  <p>And an Albums table : </p>
  
  <pre><code>  albumid   | album_name        | owner_id
      1       | My Holiday 2012   |    1
      2       | My Holiday 2013   |    6
  </code></pre>
  
  <p>And I want to get all photos contained within all albums for a given <code>owner_id</code></p>
  
  <p>I can do the following :</p>
  
  <pre><code>select albums.*, photos.*
  from photo_albums as albums
  left join photos as photos
  on albums.albumid = photos.albumid
  where albums.owner_id = :owner_id
  </code></pre>
  
  <p>This will return 2 rows, on for each photo as well as the relevant album data.</p>
  
  <p>However what I would like to do is return only on row per album.  With the photos rows nested within each album row.</p>
  
  <p>Returning output similar to the following:</p>
  
  <pre><code>[{
      albumid : 1,
      album_name : My Holiday 2012,
      owner_id : 1,
      photos : {
                   photoid : 1,
                   filename : something.jpg
               },
               {
                   photoid : 2,
                   filename : somethingelse.jpg
               }   
  }]
  </code></pre>
  
  <p>Is there an easy and efficient way to do this within the MySQL query itself?  If so how?  Or alternatively am I better taking the complete row data from the original query and constructing the data into this output by looping through the rows in php?</p>
  
  <p>Thanks in advance</p>