PC Services

 

Some PHP Tips and 'Tricks'

Thinking beyond each page and DESIGNING your web site

Tel: 0118 946 3634
Email

facebook logo
Follow Us on Facebook

   The Company 
   Resources 
     Dropdowns and ENUM 
   Commentaries           
   Personal 

HTML/PHP Populating Dropdown from ENUM values in Form

This is more how to write the PHP generated (X)HTML, whilst using only allowed values from an ENUM or SET datatype.

Often on good SQL databases you will use ENUM or SET datatypes for fields (columns), but it would be more useful to use the values in the database instead of hardcoding these into scripts for maintainability. To this end it would be useful to read back from the database what the list of ENUM/SET values are, to fill dropdown selections for editing records or on search forms.

Thankfully this is possible from using the SQL command 'SHOW COLUMNS' to get the column attribute. One part of the attributes for an ENUM is the string enum( 'value1', 'value2', 'value3' ), so some string processing and array creation makes this possible to provide the data in a useable format to incorporate in dropdown lists or even data checking functions.

Functions shown below, extract the values from the column attributes, and fill a HTML dropdown list.

// Get Enum or Set value names from table for drop downs etc
// returns false for fail or array of values
function getEnumFieldValues( $tableName, $field )
{
$field_query = mysql_query( "show columns from $tableName where Field='$field'" );
if( mysql_num_rows( $field_query ) <= 0 )
  return false;
$fieldDetail = mysql_fetch_array( $field_query );
$type = preg_replace( '/(^set\()|(^enum\()|\'|\)/i', '', $fieldDetail[ 'Type' ] );
$fieldarray = explode( ',', $type );
return $fieldarray;
}

// selectEnumValues - create options in drop down for enum values
// calls another function to perform
function selectEnumValues( $tableName, $field, $selected )
{
selectEnumIndent( $tableName, $field, $selected, "" );
}

// selectEnumIndent - create options in drop down for enum values with indent
// extra indent variable to align HTML output
//
// database must be open uses current connection
// $tableName - table to read
// $field - field name to read
// $selected - empty for not selected
//           - current row and field value to highlight as selected
// $indent - string of padding characters to fill left side with
//
// Reads values using getEnumValues then populates a series of HTML
// <OPTION></OPTION> statements if there is a match with $selected then
//  adds 'SELECTED' tag for that line.
function selectEnumIndent( $tableName, $field, $selected, $indent )
{
// read the array of values
$list = getEnumFieldValues( $tableName, $field );
// scan the array
foreach( $list as $val )
  {
  echo $indent,"<option value=\"" . $val . "\"";
  if( $val === $selected )
    echo " SELECTED";
  echo ">" . $val . "</option>\n";
  }
}

An example of usage, that will create a dropdown from ALL the ENUM valid values and show the curent selection as the default selection -

   <tr>
     <td align=left><B>Active</B></td>
     <td> &nbsp;<select name="active" title="Please select Record Active or Not">
<?php selectEnumIndent( 'test2', 'Test', $row[ 'Test' ], "              " );
?>           </SELECT></td>
   </tr>

This creates the HTML code below (note the SELECTED option), the space string indents the <OPTION> tags for easy reading.

   <tr>
     <td align=left><B>Active</B></td>
     <td> &nbsp;<select name="active" title="Please select Record Active or Not">
              <option value="No">No</option>
              <option value="Yes" SELECTED>Yes</option>
              <option value="Maybe">Maybe</option>
              <option value="File Not Found">File Not Found</option>
           </SELECT></td>
   </tr>

As the select tags are defined outside the function, and the parameters define the table, field and selected value, these functions can be called multiple times on the same form or page.

© 2010 onwards by PC Services, Reading UK Last Updated: 9th June 2011
If you encounter problems with this page please email your comments to webmaster