Clean way to get foreign key objects in PHP MySQL query

Question

I use the following code to get a book object from my MySQL database.

$q = $pdo->prepare('SELECT 
                        book_id "id", 
                        book_title "title", 
                        book_slug "slug"
                    FROM book 
                    WHERE book_id=:id');
$q->bindParam(':id', $id, PDO::PARAM_INT);
$q->execute();
$book = $q->fetchObject('Book');

Books have a many-to-many relation to songs, which have a song_id, song_title and song_lyrics, so I have a book_song table with book_id and song_id.

I'd like to get all the songs belonging to a book. Is there a clean way to do this in one query?

What I'm looking for here is how to write the query in a clean way and also how to process the results to end up with a Book object which has an array of Song objects without too much code.

Answer 1

The JOIN that gillyspy suggested would be how an ORM would handle this, but if you're using just PDO, then you have to handle all the mappings yourself (and the looping, and detecting when to create a new object, etc.) This looks to me like you've crossed the threshold where it makes sense to consider an ORM framework (such as http://www.phpactiverecord.org/). If you add even one more one-to-many association, the mappings are going to make your brain hurt.