Dropdown Select With PHP and MySQL
Create Dynamic Drop down Selects
By Kevin WatersonContents
Abstract
PHP is an excellent tool for dealing with form values and UPDATEing databases with the values from them. In this tutorial the tables are turned and the form is created by PHP using values from the database. The most common issue faced by PHP developers is generating dropdown lists from arrays and database result sets.
Embedded Code
The PHP language origins had code mostly embedded within HTML documents. For many this remains a simple way of creating small sites, and PHP does an excellent job when used in this fashion. These sites tend to be smaller in size and serve mostly static content, however, when small amounts of dynamic content are required, PHP fills the bill.
In this example a database connection is made using PDO and the PHPRO periodic table of elements database. A simple form is created with a HTML select. Then PHP code is embedded directly to connect to the database and fetch the results. As the script uses PDO, the result set can be iterated over directly with a simple foreach and the option for the select echo'ed to the browser.
<form>
<dl>
<dt>Name</dt>
<dd>
<input type="text" name="my_text" />
</dd>
<dt>Favorite Element</dt>
<dd>
<select name="element">
<?php
try
{
/*** query the database ***/
$result = DB::getInstance()->query("SELECT atomicnumber, english FROM elements");
/*** loop over the results ***/
foreach($result as $row)
{
/*** create the options ***/
echo '<option value="'.$row['atomicnumber'].'"';
if($row['atomicnumber']==42)
{
echo ' selected';
}
echo '>'. $row['english'] . '</option>'."\n";
}
}
catch(PDOException $e)
{
echo 'No Results';
}
?>
</select>
</dd>
</dl>
</form>
In the above code the dropdown code is embedded directly into a simple HTML form. Most of the code is simple HTML so this method is good when not much PHP will be used. Many templates that us PHP as the template language may use this form of dropdown creation and simply pass the result array to the template itself.
A Dropdown Function
In this example, the dropdown select is generated within a function. The function takes three arguments to achieve this.
- $name - the name and id of the select
- $options - the array of options for the dropdown
- $selected - an optional argument if one of the dropdown is to be pre-selected
This time the dropdown is created wholly within the function and the completed dropdown select is returned. This method has the advantage of being a re-usable snippet that could be included in any project or form creation class.
This function does not make use of the database, so an array would need to be created from the database result set prior to passing the array to the function as an arguement.
<?php
/**
*
* @create a dropdown select
*
* @param string $name
*
* @param array $options
*
* @param string $selected (optional)
*
* @return string
*
*/
function dropdown( $name, array $options, $selected=null )
{
/*** begin the select ***/
$dropdown = '<select name="'.$name.'" id="'.$name.'">'."\n";
$selected = $selected;
/*** loop over the options ***/
foreach( $options as $key=>$option )
{
/*** assign a selected value ***/
$select = $selected==$key ? ' selected' : null;
/*** add each option to the dropdown ***/
$dropdown .= '<option value="'.$key.'"'.$select.'>'.$option.'</option>'."\n";
}
/*** close the select ***/
$dropdown .= '</select>'."\n";
/*** and return the completed dropdown ***/
return $dropdown;
}
?>
<form>
<?php
$name = 'my_dropdown';
$options = array( 'dingo', 'wombat', 'kangaroo' );
$selected = 1;
echo dropdown( $name, $options, $selected );
?>
</form>
This method has the benefit of not only being portable, but of providing superior separation of PHP code and HTML markup. For templating or use with MVC style applications, this type of function can be used repeatedly for many different dropdowns by simply supplying the appropriate arguments.
When dealing with arrays or database result sets, the array will normally consist of a key/value pair that relate to a product in a cart, or other object. This is why the array key (index) variable is used for the value of the each option, and the visible name of the select is the name of the product or array member.
Multiple Selects
Up to this point the select dropdowns that have been created specified only a single possible pre-selected item within the options list. HTML form selects also provide for the possibility of multiple selects. This enables users to select multiple and submit them with the rest of the form.
In this example, an array of pre-selected items is used and each option value is checked against this array . If the option value is found within the array of selected items, it is marked as selected and when displayed, the option is displayed as selected. Like the previous function, if the results are coming from a database, an array would need to be created from the database result set and then passed to the function as an arguement.
<form>
<?php
/**
* @multi select dropdown menu
*
* @param string $name
*
* @param array $options
*
* @param array $selected (default null)
*
* @param int size (optional)
*
* @return string
*
* /
function multi_dropdown( $name, array $options, array $selected=null, $size=4 )
{
/*** begin the select ***/
$dropdown = '<select name="'.$name.'" id="'.$name.'" size="'.$size.'" multiple>'."\n";
/*** loop over the options ***/
foreach( $options as $key=>$option )
{
/*** assign a selected value ***/
$select = in_array( $option, $selected ) ? ' selected' : null;
/*** add each option to the dropdown ***/
$dropdown .= '<option value="'.$key.'"'.$select.'>'.$option.'</option>'."\n";
}
/*** close the select ***/
$dropdown .= '</select>'."\n";
/*** and return the completed dropdown ***/
return $dropdown;
}
?>
<form>
<?php
$name = 'multi_dropdown';
$options = array( 'dingo', 'wombat', 'kangaroo', 'steve irwin', 'wallaby', 'kookaburra' );
$selected = array( 'dingo', 'kangaroo', 'kookaburra' );
echo multi_dropdown( $name, $options, $selected );
?>
</form>
This method provides a re-usable function much the same as the previous function with the difference that an array is used for the select to provide multiple options. Note that a fourth optional parameter has been added to specify the number of options displayed in the multi-select list. This defaults to 4 as seen in the example.