Build Select List From Table

Description: This function builds an HTML select list from any mysql table.

<?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 w3mentors of how you can use this function */
 
/* Simple w3mentor */
ECHO html_drop_down ("formvar1_prod_type"
                     ,"prod_types"
                     ,"prod_type_id"
                     ,"name");
 
/* An w3mentor 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 w3mentor 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 w3mentor, it should work with any table though:
 
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)
*/
 
?>

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.