Page 1 of 1

Populate a second listbox based on the selection of a first

Posted: Mon Jul 07, 2008 7:39 am
by graytest
Please use appropriate BBcode tags to enclose code listings, as I have done for you, below.

Hi All

I am teaching myself PHP/MYSQL and I wonder if you can help me?

I've been researching how to present two listboxes on a webpage (html form). The first is a country selection box, the second a city selection box. When a country is selected in the first, I want to perform an SQL query to retrieve city-name records based on that selection and populate the second "city" listbox. The user will then select a city and press a submit button whereupon I want to store users' country and city selections into a table.

I've done a fair bit of programming in my time and I kind of assumed that this would be a relatively simple thing to do? However, the research I've done suggests this is not so, with references to Javascript and AJAX etc etc.

I'm amazed that this appears to be so involved when it must be one of the most commonly used concepts on web pages? Have I misunderstood or is this very simple to do with just PHP and MYSQL?

I've taught myself how to select the country records, to populate the country listbox and to detect the selected country but I am really struggling with how to trigger the retrieval of the city records. I can do it if I put a submit button next to the country listbox and duly populate the city listbox but I just cannot seem to be able to select the city?

I have my code below - can someone explain where I am going wrong please? Do I have to name the SUBMIT or SELECT items for the country and city sections with different names or something?

Thanks


-----------------------------------------------------------------------

Code: Select all

<?
// Connect to MYSQL database
$dbconn = mysql_connect('localhost', myuser, mypassword)or die('Could not connect: ' . mysql_error());
echo 'Connected successfully 2';
// Select xxxxx database
mysql_select_db(mydatabase) or die('Could not select database');
?> <br> <?echo 'Selected Database OK'; ?> <br> <?
 
// If submitted, check the value of "select". If it is not blank value, get the value and put it into $select.
if(isset($select)&&$select!=""){
$select=$_GET['select'];
}
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
</head>
<body>
<form id="form1" name="form1" method="get" action="<? echo $PHP_SELF; ?> ">
 
COUNTRY :
<select name="select">
<option value="">--- Select ---</option>
<?
// Get records from database (table "countries_table").
$list=mysql_query("select * from countries_table order by country_id asc");
 
// Show records by while loop.
while($row_list=mysql_fetch_assoc($list)){
?>
<option value="<? echo $row_list['country_id']; ?>" <? if($row_list['country_id']==$select){ echo "selected"; } ?>><? echo $row_list['fvCaption']; ?></option>
<?
// End while loop.
}
?>
</select> 
<input type="submit" name="Submit" value="Select" />
</form>
<hr>
<p>
<?
// If you have selected from list box.
if(isset($select)&&$select!=""){
 
// Get records from database (table "countries_table").
$result=mysql_query("select * from countries_table where country_id='$select'");
$row=mysql_fetch_assoc($result);
?>
You have selected <strong><? echo $row['fvCaption']; ?></strong> as your country...</p>
 
<?
// End if statement. 
}
?>
 
CITY :
 
<select name="select">
<option value="">--- Select ---</option>
<?
// Get records from database (table "city_table").
$list=mysql_query("select * from city_table where country_id='$select'");
 
// Show records by while loop.
while($row_list=mysql_fetch_assoc($list)){
?>
<option value="<? echo $row_list['city_id']; ?>" <? if($row_list['city_id']==$select){ echo "selected"; } ?>><? echo $row_list['fvCaption']; ?></option>
<?
// End while loop.
}
?>
</select> 
<input type="submit" name="Submit" value="Select" />
</form>
<hr>
<p>
<?
// If you have selected from list box.
if(isset($select)&&$select!=""){
 
// Get records from database (table "city_table").
$result=mysql_query("select * from city_table where city_id='$select'");
$row=mysql_fetch_assoc($result);
?>
You have selected <strong><? echo $row['fvCaption']; ?></strong> as your city...</p>
 
<?
// End if statement. 
}
 
 
// Close database connection.
mysql_close();
?>
</p>
</body>
</html>

Re: Populate a second listbox based on the selection of a first

Posted: Mon Jul 07, 2008 11:39 am
by crmalibu
Use

Code: Select all

or

Code: Select all

tags when posting.

For starters, use different field names in the html.
 You need a way to know which select menu the user is submitting a value from. When you have multiple fields in a form with the same name, while the browser will generally send both fieldname/values back to your server, php must pick only one, and you dont know which one.

You also close the <form> tag prematurely.

You may have other issues(didnt look too closely).

Re: Populate a second listbox based on the selection of a first

Posted: Mon Jul 07, 2008 3:25 pm
by califdon
graytest wrote:I've done a fair bit of programming in my time and I kind of assumed that this would be a relatively simple thing to do? However, the research I've done suggests this is not so, with references to Javascript and AJAX etc etc.

I'm amazed that this appears to be so involved when it must be one of the most commonly used concepts on web pages? Have I misunderstood or is this very simple to do with just PHP and MYSQL?
Web programming has some unique characteristics. Part of the processing is done by the web server (which is where PHP is executed) and part is done by the browser client (which is where Javascript and HTML is interpreted). If you send a page to a browser, PHP is done, it is no longer there, it cannot possibly respond to user actions. If you need to retrieve data from a database on the server, you have to send another request to the server. You can do that in either of two ways: either by submitting a form, running a script that retrieves the data and sending an entirely new page back to the requestor, or by using a technique that is called an "asynchronous request", usually using AJAX, that sends only the new data to a Javascript function that does whatever you need to do with it, without refreshing the entire page.

AJAX results in a much smoother user experience, but suffers the disadvantage that it requires Javascript to be enabled in the client browser. I've seen statistics that indicate that something like 5% of browsers have had Javascript disabled by their users, presumably as a security measure.