Build HTML select list from table

Submitted by:Jhon Brain

Date added:11 August, 2014

Category:PHP

This function builds an HTML select list from from data retrieved from a mySQL table.

Tags: select list , retrieve data

Code 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)
*/

?>
 
 

Comments