Home >> Snippet List >> Snippet

Name
Build HTML select list from table
Description
This function builds an HTML select list from from data retrieved from a mySQL table
PHP Snippet
<?PHP 
 
FUNCTION html_drop_down ($in_name 
                            ,$in_tablename 
                            ,$in_value_column_name 
                            ,$in_display_column_name 
                            ,$in_other_value = "" 
                            ,$in_other_display = "" 
                            ,$in_selected_value = "" 
                            ,$in_where_clause = "") { 
 
    // build the SQL string 
    $sql = "SELECT " . $in_value_column_name . " col_val," . 
                       $in_display_column_name . " col_disp" . 
           " FROM " . $in_tablename; 
    IF ($in_where_clause) { 
        $sql .= " " . $in_where_clause; 
    } 
    $sql .= " order by col_disp"; 
 
    // connect to mysql, put your specifics here. 
    MYSQL_PCONNECT() or DIE ("problems connecting to Database."); 
 
    // queries the database, be sure to name your database. 
    $result = MYSQL_DB_QUERY("ecomm",$sql); 
 
    // build the select list. 
    IF ($result) { 
       $return_value = "<SELECT NAME=\"$in_name\">"; 
 
       IF ($in_other_display) { 
           $return_value .= "<OPTION VALUE=\"$in_other_value\"> $in_other_display </OPTION>"; 
       } 
 
       WHILE ($rec = MYSQL_FETCH_ARRAY($result)) { 
 
           $return_value .= "<OPTION "; 
 
           IF ($in_selected_value) { 
               IF ($rec["col_val"] == $in_selected_value) { 
                   $return_value .= " SELECTED "; 
               } 
           } 
 
           $return_value .= " VALUE=\"" . $rec["col_val"] . "\"> " . 
                            $rec["col_disp"] . " </OPTION>"; 
        } 
 
        $return_value .= "</select>"; 
    } ELSE { 
        $return_value .= "No data."; 
    } 
 
    MYSQL_FREE_RESULT($result); 
 
    RETURN $return_value; 
 
} 
?> 
 
<? 
/* Here's three examples of how you can use this function */ 
 
/* Simple example */ 
ECHO html_drop_down ("formvar1_prod_type" 
                     ,"prod_types" 
                     ,"prod_type_id" 
                     ,"name"); 
 
/* An example with an extra value in the list, good for N/A, All values for queries etc.. */ 
ECHO html_drop_down ("formvar2_prod_type" 
                    ,"prod_types" 
                    ,"prod_type_id" 
                    ,"name" 
                    ,"-1" 
                    ,"All Types"); 
 
/* An example with a where clause */ 
ECHO html_drop_down ("formvar3_prod_type" 
                    ,"prod_types" 
                    ,"prod_type_id" 
                    ,"name" 
                    ,"-1" 
                    ,"All Types" 
                    ,"3" 
                    ,"WHERE prod_type_id <> 4"); 
 
 
/* Here's the data used in my example, it should work with any table: 
 
mysql> select prod_type_id, name from prod_types; 
+--------------+-------------------+ 
| prod_type_id | name              | 
+--------------+-------------------+ 
|            1 | Mother Boards     | 
|            2 | Graphics Cards    | 
|            3 | Memory            | 
|            4 | Pointing Devices  | 
|            5 | Keyboards         | 
|            6 | Operating Systems | 
+--------------+-------------------+ 
6 rows in set (0.00 sec) 
*/ 
 
?>