Here is a little function I put together to create dynamic drop down lists powered by two MySQL fields.
Have a look at the code. I'll explain a bit more after it.
PHP Code:
<?php
/**
* @author Roger Thomas
* @copyright 2010
*
*
* This function takes in 7 variables
* Name of the select box
* Index Field (mysql field)
* Value Field (mysql field)
* Which index is selected // not required
* Table (which mysql table is to be used)
* Order By (which field to order by) // not required
* Order direction (ASC or DESC) // not required
*
* example output:
* <select name="$name">
* <option value="$index">$value</option>
* <option value="$index">$value</option>
* </select>
*/
function dropdownMySQL($name,$index,$value,$selected,$table, $orderby,$orderdirection) {
$dbhost=""; // correct with your database host
$dbname=""; // correct with your database name
$dbuser=""; // correct with your database username
$dbpass=""; // correct with your database password
$con=mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
if ($orderby!="" && $orderdirection!="") {
$additionalSQL=" ORDER BY ".$orderby." ".$orderdirection."";
}
else {
$additionalSQL="";
}
$query="SELECT ".$index.",".$value." FROM ".$table."".$additionalSQL."";
$result=mysql_query($query);
if (mysql_num_rows($result)!="0") {
$str = "
<select name="".$name."">
";
while ($row=mysql_fetch_array($result)) {
if ($row[$index]==$selected) { $thisExtra=" SELECTED="SELECTED""; }
else { $thisExtra=""; }
$str .= "
<option value="".$row[$index].""".$thisExtra.">".$row[$value]."</option>
";
}
$str .= "
</select>
";
return $str;
}
else {
$error="Error with results";
return $error;
}
}
?>
And here's how to use the function...
PHP Code:
<?php
/**
* EXAMPLE BELOW SHOWS US SELECTING...
*
* Columns called "id" and "name" from the "test" table and outputting
* into a select box called "selectname".
* The results are ordered by id DESC (so the highest id is listed first.)
* In addition, we are saying that by default, the option with an index of "12"
* should be selected.
*/
echo dropdownMySQL("selectname","id","name","12","test","id","DESC");
?>
So, obviously you will need to place your database settings into the function (or use an external configuration file), but I wanted to keep this all neat and tidy (and completely independent).
What you end up with is a fully completed select box ready for your users to pick and choose a value from. (it probably needs to be noted that you may need to convert values to their HTML entities if your fields hold any non alpha-numeric values).
When I first started to learn PHP, this is what I actually wanted to accomplish. Funny how things progress...