Database driven dynamic select boxes

Anon

Hi,

I am currently developing a website backed by a MySQL database. My pages have been written in HTML with some Javascript. Using PHP to access the DB. I assume I need to use PHP for the following:

Users will fill in a form to register. On that form I am looking to have a dropdown box called 'country' which I would like to be populated from a table called, er...'country'. That's the first bit I would like help on.

Then, to make things more complex, I want another select box called 'region' to be populated by the 'region' table dynamically (depending on the country selected).

The 'region' table is linked to the 'country' table by a country code field - a list of regions apply to each country code.

I know it's specific and I feel I'm asking you to do my dirty work. However, I hope I've explained myself ok and it'd be very much appreciated if any of you clever people can help me out!

Thanks very much,

Sara.

Anon

Sarah,

see below and see whether it is useful for you.

#######################################################################

/ This function converts the value in a SQL statement to a select box. /

function query_select($name, $query, $default="")
{

$result = mysql_query($query);

if (!$result)
	return(0);

$select  = "<SELECT NAME=\"$name\">";

$row = mysql_numrows($result);

for ($i=0; $i < $row; $i++) 
{
		$opt_code = mysql_result($result, $i, 0);
		$select .= "<OPTION VALUE=\"$opt_code\"";

	if ($opt_code == $default) 
	{
  		$select .= " SELECTED";
		}
	$select .=  ">[$opt_code] </OPTION>";
}

$select .= "</SELECT>\n";

return($select);

}
#######################################################################

Anon

Thanks Kelvin,

That should be of considerable help. I'm just not sure how I apply it to my code:

Can I put that into the existing html form, if so, how?
OR, is it better just to rewrite the whole form in php on the cgi server?

Any further advice from anyone still very much appreciated.

Thanks again for your help thus far,

Sarah.

kennethl

That code will need to be in a php file in order to run. You can't get dynamic content in a straight HTML file.

As for your question about a "region" field whose options will change dependent on your "country" field, it sounds like you'll need a client-side script to make that work -- say VBScript or JavaScript.

Anon

Thanks again.

One final question, I promise! Find my script so far below...it works but I am now looking to 'link' the two boxes.

Is it not possible to use "onChange" to get the value from the first select box and use it as the SQL WHERE clause for the 2nd box (to replace the ?????????? in the script below)? It would therefore retrieve the regions for the selected country.

Hope you can help again. Thank you,

Sarah.

<?php
function query_select($name, $query, $default="")
{
$result = mysql_query($query);

if (!$result)
return(0);
$select = "<SELECT NAME=\"$name\">";
$row = mysql_numrows($result);
for ($i=0; $i < $row; $i++)
{
$opt_code = mysql_result($result, $i, 0);
$select .= "<OPTION VALUE=\"$opt_code\"";
if ($opt_code == $default)
{
$select .= " SELECTED";
}
$select .= ">$opt_code </OPTION>";
}
$select .= "</SELECT>\n";
return($select);
}
$name = "country";
$query = "SELECT name FROM country";
$box = query_select($name, $query);
echo "$box";

$name = "region";
$query = "SELECT name FROM region WHERE country = ???????????????";
$box2 = query_select($name, $query);
echo "$box2";
?>

kennethl

I can't think of how you'd get PHP to execute a query to populate your "region" select box based on the "coutry" selection without using a submit button.
Only way I can imagine you could get that to work is to get PHP to bring in all possible regions for all possible countries initially, and then use the onChange to display the relevant regions.