[SOLVED] 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.

[GingerRobot]

The problem is that you're re-assigning a value to $row with the second while statement. Therefore, after the first time the first while loop has executed, $row has been overwrittin, and so the while loop finishes. Try renaming the second $row to something else.

[atlanta]

yea make is  like this

 

$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'";
   $result2 = mysql_query($query4);
$hour = $row['Hour'];
$minute = $row['Minute'];
       while ($row2 = mysql_fetch_assoc ($result2)) 
{
$hour = $row2['Hour'];
$minute = $row2['Minute'];
	echo "("."$hour".":"."$minute";
	if ($minute<10){echo"0";}
	echo")";
}
}

[jhenary]

Thanks so much! Maybe next time I should take a PHP class before the DB class