drop downs from table data

worldop · 12-04-2008, 11:02 PM

Hello, I'm developing a booking system. I've got a database, with records such as( colon representing column break ):
Bristol : Newcastle
Bristol : Manchester
Manchester : Bristol
and so on, I'm trying to make drop down menu's for Departing City, and Destination City using <select><option> html. I'm getting duplicate drop-down options and im not sure how to display one of each of the departing/destination records, Also another thing I don't know how to do is, only display the corresponding Destination: e.g.
if the Departing City is Bristol, then only the destinations from Bristol should appear in the second drop-down.

Here is my code so far, if anyone could help it would be appreciated, thanks.

php Code:

$Query = "SELECT DISTINCT DepartingFrom FROM flights";
$RunQuery = mysqli_query($dbc, $Query) or trigger_error("Query: $Query\n<br />MySQL Error: " . mysqli_error($dbc));


if(mysqli_num_rows($RunQuery) > 0){
    echo '<h1>Book your flight here</h1><br />
            <b>Airport to fly from:</b>';
    echo '<select>';
    while($Row = mysqli_fetch_array($RunQuery)){
            echo '<option>' . $Row['DepartingFrom'] . '</option>';
        } /* end of while loop */         
            echo '</select><br />';
            echo '<b>Airport to Fly to:</b>
            <select>';
            $Query = "SELECT ArrivalAt FROM flights";
            $RunQuery = mysqli_query($dbc, $Query) or trigger_error("Query: $Query\n<br />MySQL Error: " . mysqli_error($dbc)); 
            while($Row = mysqli_fetch_array($RunQuery)){
                echo'<option>' . $Row['ArrivalAt'] . '</option>';
            } /* end of while loop */
            echo'</select>';
        echo '<br />';
    
} /* end of if statement - add error if there were no rows in flights */

Wildhoney · 12-04-2008, 11:15 PM

Would this not require Ajax to change the second dropdown depending on the content of the first? So for instance, if the user selects "Bristol" from the first dropdown, should an Ajax call be made to acquire the destinations available for the chosen departure?

Take a look at Teletext Holidays and how they did it. You will notice that when you select a location to depart from, the destination list will update, displaying only those available for the previously selected departure.

The aforementioned is achieved by an Ajax call to this page, with the following POST parameters:

Code:

{"knownCategoryValues":"SearchFrom:-9;","category":"SearchGateway","stype":"Package_Holidays","departuredate":"11/12/2008"}

The response we are given is a JSON formatted response, which is then used to populate the second dropdown on-the-fly based on the first. I believe this is what you're after.

Incidentally, welcome to the community, you little devil!

I hope you enjoy your stay here.

worldop · 12-04-2008, 11:29 PM

Its exactly what I'm after :) didn't know I had to use Ajax though *tutorials here i come*
Thanks, i'll update when im done

KingOfTheSouth · 12-04-2008, 11:51 PM

I did not have a chance to answer it:( I would have gotten that one too

Wildhoney · 12-05-2008, 12:45 AM

You should possibly try Prototype with this. It will make everything so much easier for you! They also have a fantastic documentation.