As I only have one value in the first dropdown, I am trying to create a cascading dropdown in PHP that populates the second dropdown on page load.
My database has a table called 'nights' with fields called: 'city', 'name' and 'day'.
To fill my first box I'm using SELECT DISTINCT cities from nights etc which has worked fine.
To fill the second box I need something along the lines of SELECT name WHERE city = $city - my problem is that I'm not sure how to set $city (being the name of the <select> tag). I can't use $_POST['city'] because the form hasn't been sent at this point.
Any ideas?
If you want this to be dynamic (i.e. after the user changes the dropdown) you will have to use javascript to firstly query a PHP page (probably using jQuery get) then adjust the dropdowns accordingly. There are lots of tutorials for this on the web.
If you just want the initial page data to be populated you can pick the first city from your query and set the option as selected, then use that city in your next query.
Something like:
$first = True;
while($row = mysql_fetch_array($result))
{
echo "<option" . (($first) ? " selected" : "") . ">" . $row['city'] . "</option>";
if($first)
{
$first = !$first;
$city = $row['city'];
}
}
//now do stuff with with $city
<?
$CITIES = query_result("SELECT DISTINCT cities FROM nights;");
if (isset($_POST["city"])) {
$NAMES = query_result("SELECT name FROM nights WHERE city = '{$_POST[city]}'");
if (isset($_POST["day"])
$DAYS = query_result("SELECT day FROM nights WHERE city='{$_POST[city]}'".
" AND name = '{$_POST[name]}'");
else
$DAYS = array();
} else
$NAMES = array();
/* now output the <select> markup for $CITIES, $NAMES and $DAYS */
?>
Note: you have to define the query_result function yourself (I just used it to simplify the code).
You can use AJAX for this.
On change event call javascript-ajax function that call php script which makes you second dropdown caode.
You will need to use ajax to load options from php script.
Here is an example of how you can do it with jquery and ajax:
Autopopulate Select Dropdown Box Using jQuery
