I am working on a script which require advance search
I have 4 tables and I want to connect each table in the search.
First drop down list would be cinema
Second depending on the cinema is the movie
Third depending on the movie is the date and
fourth depending on the date is the time together with the room.

Cinema table
Cinema_id Cinema_name
PK

Movie table
Movie_id Movie_name
PK

Room table
Room_id Room_name
PK

Movie_status
Movie_time Movie_id Room_id Cinema_id
PK FK FK FK


Here is the code i'm using but i can't get it work right in the 3rd and 4th selection in the list.


<?php

$cinema = $movie = $date = $time = null; //declare vars

$conn = mysql_connect('localhost', 'username', '1password');
$db = mysql_select_db('project',$conn);

if(isset($_GET["cinema"]) && is_numeric($_GET["cinema"]))
{
    $cinema = $_GET["cinema"];
}

if(isset($_GET["movie"]) && is_numeric($_GET["movie"]))
{
    $movie = $_GET["movie"];
}

if(isset($_GET["date"]) && is_numeric($_GET["date"]))
{
    $date = $_GET["date"];
}

if(isset($_GET["time"]) && is_numeric($_GET["time"]))
{
    $time = $_GET["time"];
}

?>

<script language="JavaScript">

function autoSubmit()
{
    var formObject = document.forms['theForm'];
    formObject.submit();
}

</script>

<form name="theForm" method="get">

<select name="cinema" onChange="autoSubmit();">
        <option value="null"></option>

    <?php


        $sql = "SELECT * FROM cinema";
        $cinemas = mysql_query($sql,$conn);

        while($row = mysql_fetch_array($cinemas))
        {
            echo ("<option value=\"$row[cinema_id]\" " . ($cinema == $row["cinema_id"] ? " selected" : "") . ">$row[cinema_name]</option>");
        }

        ?>

    </select>


    <br><br>

    <?php

    if($cinema != null && is_numeric($cinema))
    {

    ?>

    <select name="movie" onChange="autoSubmit();">
        <option value="null"></option>

        <?php


        $sql = "SELECT * FROM movie_status s,movie m WHERE s.cinema_id = $cinema and s.movie_id = m.movie_id";
        $movies = mysql_query($sql,$conn);

        while($row = mysql_fetch_array($movies))
        {
            echo ("<option value=\"$row[movie_id]\" " . ($movie == $row["movie_id"] ? " selected" : "") . ">$row[movie_name]</option>");
        }

        ?>

    </select>

    <?php

    }

    ?>

    <br><br>

    <?php

    if($movie != null && is_numeric($movie) && $cinema != null)
    {

    ?>

    <select name="date" onChange="autoSubmit();">
        <option value="null"></option>

        <?php


        $sql = "SELECT s.movie_date
FROM cinema c, movie_status s, movie m
WHERE c.cinema_id = s.cinema_id
AND s.movie_id = m.movie_id
AND c.cinema_name = $cinema
AND m.movie_name = $movie";
        $dates = mysql_query($sql,$conn);

        while($row = mysql_fetch_array($dates))
        {
            //echo ("<option value=\"$row[movie_id]\" " . ($date == $row["movie_id"] ? " selected" : "" . ">$row[movie_date]</option>");
              echo ("<option value=\"$row[$sql]\</option>");
        }

        ?>

    </select>

    <?php

    }

    ?>

    <br><br>

    <?php

    if($date != null && is_numeric($date) && $cinema != null && $movie != null)
    {

    ?>

    <select name="time" onChange="autoSubmit();">
        <option value="null"></option>

        <?php


        $sql = "SELECT t.movie_time,t.room_id
FROM cinema c, movie_status t, movie m
WHERE c.cinema_id = t.cinema_id
AND t.movie_id = m.movie_id
AND c.cinema_name = $cinema
AND m.movie_name = $movie
and t.movie_date= $date";
        $times = mysql_query($sql,$conn);

        while($row = mysql_fetch_array($times))
        {
            echo ("<option value=\"$row[movie_id]\" " . ($time == $row["movie_id"] ? " selected" : "") . ">$row[movie_time]</option>");
        }

        ?>

    </select>

    <?php

    }

    ?>

</form>


Special thanks to anyone that can help me out.
Thank you.