Reading Related Tables As Nested Rows

Problem

A problem I used to face with relational databases is how to handle 1-to-many or many-to-many rows in SQL select statements. Let's say you have 2 tables with a many-to-many relationship between them, such as a Movies table and an Actors table. How do I present a list of movies together with their associated actors?

The naive way to do this is by using nested loops with a query for movies in the top loop and a query for actors in the second loop. In PHP it would look something like...

$moviesResult = mysql_query("SELECT id, title FROM movies"); 
while($moviesRow = mysql_fetch_assoc($result)) 
{ 
    echo("<h1>" . $moviesRow['title'] . "</h1>"); 

    echo("<ul>"); 
    $actorsResult = mysql_query("SELECT actors.name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid WHERE movies_actors.movieid = " . $moviesRow['id'] . ";"); 
    while($actorsRow = mysql_fetch_assoc($actorsResult)) 
    { 
        echo("<li>" . $actorsRow['name'] . "</li>"); 
    } 
    echo("</ul>"); 
}

This approach however will kill your database. Ideally you should minimize the number of queries sent.

Another approach would be to join the movies table with the actors table and then read it with nested loops.

$result = mysql_query("SELECT movies.id AS id movies.title AS title, actors.name AS actor FROM movies INNER JOIN movies_actors ON movies.id = movies_actors.movie INNER JOIN actors ON actors.id = movies_actors.actor"); 
$row = mysql_fetch_assoc($result); 
while($row) 
{ 
    $currMovieId = $row['id']; 
    echo("<h1>" . $row['title'] . "</h1>"); 
    echo("<ul>"); 
    do 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
    } while ($row = mysql_fetch_assoc($result) && $row['id'] == $currMovieId);
    echo("</ul>"); 
}

This works but as soon as you add another table to the join, determining which rows contain new information can be a nightmare, not to mention all the rows which just contain repeated information due to the cartesian product. For example:

Id	Title	Actor	Genre
1	The Matrix	Keanu Reeves	Action
1	The Matrix	Keanu Reeves	Adventure
1	The Matrix	Keanu Reeves	Sci-Fi
1	The Matrix	Laurence Fishburne	Action
1	The Matrix	Laurence Fishburne	Adventure
1	The Matrix	Laurence Fishburne	Sci-Fi
1	The Matrix	Carrie-Anne Moss	Action
1	The Matrix	Carrie-Anne Moss	Adventure
1	The Matrix	Carrie-Anne Moss	Sci-Fi
2	The Matrix Reloaded	Keanu Reeves	Action
2	The Matrix Reloaded	Keanu Reeves	Adventure
2	The Matrix Reloaded	Keanu Reeves	Sci-Fi
2	The Matrix Reloaded	Laurence Fishburne	Action
2	The Matrix Reloaded	Laurence Fishburne	Adventure
2	The Matrix Reloaded	Laurence Fishburne	Sci-Fi
2	The Matrix Reloaded	Carrie-Anne Moss	Action
2	The Matrix Reloaded	Carrie-Anne Moss	Adventure
2	The Matrix Reloaded	Carrie-Anne Moss	Sci-Fi
3	The Matrix Revolutions	Keanu Reeves	Action
3	The Matrix Revolutions	Keanu Reeves	Adventure
3	The Matrix Revolutions	Keanu Reeves	Sci-Fi
3	The Matrix Revolutions	Laurence Fishburne	Action
3	The Matrix Revolutions	Laurence Fishburne	Adventure
3	The Matrix Revolutions	Laurence Fishburne	Sci-Fi
3	The Matrix Revolutions	Carrie-Anne Moss	Action
3	The Matrix Revolutions	Carrie-Anne Moss	Adventure
3	The Matrix Revolutions	Carrie-Anne Moss	Sci-Fi

Are we supposed to receive all those rows just to display the below?

1	The Matrix	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi
2	The Matrix Reloaded	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi
3	The Matrix Revolutions	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi

Solution

The best solution I found is a hybrid of the above 2 solutions. You make a different query for each table and then read all the rows returned by each query, placing the information in the list it belongs to.

$moviesResult = mysql_query("SELECT id, title FROM movies;"); 
$actorsResult = mysql_query("SELECT movies_actors.movieid AS movieid, actors.name AS name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid;");
$genresResult = mysql_query("SELECT movies_genres.movieid AS movieid, genres.name AS name FROM genres INNER JOIN movies_genres ON genres.id = movies_genres.genreid;"); 

$actorRow = mysql_fetch_assoc($actorsResult); 
$genreRow = mysql_fetch_assoc($genresResult); 
while($movieRow = mysql_fetch_assoc($moviesResult)) 
{ 
    $currMovieId = $movieRow['id']; 
    echo("<h1>" . $movieRow['title'] . "</h1>"); 

    echo("<ul>"); 
    while ($actorRow && $actorRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $actorRow = mysql_fetch_assoc($actorsResult); 
    } 
    echo("</ul>"); 

    echo("<ul>"); 
    while ($genreRow && $genreRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $genreRow = mysql_fetch_assoc($genresResult); 
    } 
    echo("</ul>"); 
}

In this way we only make 3 queries, equal to the number of tables, and we only read as many rows as needed.