Nested SQL query help?

[jhenary]

I'm currently working on a project for a database class I'm taking. It's a simple database that displays movie showtimes and such. My problem is that when I try to nest a query inside the "while" of another query I only end up with one result. Here is the code, is what I'm trying possible?

 

$query2= "SELECT Movie_Name, MovieID, Screen FROM Movies".
	" INNER JOIN Showings ON Movies.Movie_ID = Showings.MovieID".
	" WHERE DAY(DateTime) = '$day'".
	" AND MONTH(DateTime) = '$month'".
	" AND TheaterID = '$theater'".
	" GROUP BY Screen ORDER BY Screen";
    
       $result = mysql_query($query2);
       while ($row = mysql_fetch_assoc ($result)) 
{                                           
        $movie = $row['Movie_Name'];
$movieid = $row['MovieID'];
$screen = $row['Screen'];
	echo"<A HREF=\"about.php?id=" . "$movieid" . "\" TARGET=\"_blank\">" . 
	"$movie" . "</a></br>";
   $query4 = "SELECT HOUR(DateTime) AS Hour, MINUTE(DATETIME) AS Minute FROM Showings".
" WHERE Screen = '$screen'";
   $result = mysql_query($query4);
       while ($row = mysql_fetch_assoc ($result)) 
{
$hour = $row['Hour'];
$minute = $row['Minute'];
	echo "("."$hour".":"."$minute";
	if ($minute<10){echo"0";}
	echo")";
}
}

 

What I'm trying to do is find all showings for a particular theater on a particular day and group those showings by the screen number they're playing on. For each line returned another query is run to display all show times for that day. Any help would be appreciated.