If I have two simple tables:
Photos :
photoid | filename | albumid
1 | something.jpg | 1
2 | somethingelse.jpg | 1
And an Albums table :
albumid | album_name | owner_id
1 | My Holiday 2012 | 1
2 | My Holiday 2013 | 6
And I want to get all photos contained within all albums for a given owner_id
I can do the following :
select albums.*, photos.*
from photo_albums as albums
left join photos as photos
on albums.albumid = photos.albumid
where albums.owner_id = :owner_id
This will return 2 rows, on for each photo as well as the relevant album data.
However what I would like to do is return only on row per album. With the photos rows nested within each album row.
Returning output similar to the following:
[{
albumid : 1,
album_name : My Holiday 2012,
owner_id : 1,
photos : {
photoid : 1,
filename : something.jpg
},
{
photoid : 2,
filename : somethingelse.jpg
}
}]
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?
Thanks in advance