Introduction
Creating combo box using database values is very frequent requirement. For example, one might need dynamic combo listing all categories while adding product. Creating function will reduce all efforts of writing same logic again and again.
Assumption
Database connection is already done before calling function.
Let’s get into code
Function takes following parameters
1. Table name – name of the table from which we are going the fetch the data
2. Display member – selected value of this column will be displayed
3. Value member – selected value of this column will be return on selected of item
4. Selected Id (optional) – default selected item
5. Where clause (optional) – specify where condition
6. Order by (optional) – name of column on which records are sorted in ascending order
<?
function createCombo($tableName,$valueMember,$displayMember,$selectedId=0,$where="+",$orderBy="*")
{
$displayMembers = split(",",$displayMember);
if($orderBy == "*" and $where == "+") { $whereClause = "1"; }
if($orderBy != "*" and $where == "+") { $whereClause = "1".$orderBy; }
if($orderBy == "*" and $where != "+") { $whereClause = $where; }
if($orderBy != "*" and $where != "+") { $whereClause = $where." ".$orderBy; }
$query = "select ".$valueMember.",".displayMember." from ".$tableName." where ".$whereClause." ".$orderBy;
$rs = @mysql_query($query);
$num = @mysql_num_rows($rs);
$i = 0;
if($num > 0)
{
while($row = mysql_fetch_assoc($rs))
{
$selected = ($row[$valueMember] == $selectedId?"selected='selected'":"");
$options.= "<option value='".$row[$valueMember]."' ".$selected.">";
if(sizeof($displayMembers) == 1)
{
$options.=$row[$displayMembers[0]];
}
else
{
for($j=0;$j<sizeof($displayMembers);$j++)
{
$options.=$row[$displayMembers[$j]]." ";
}
}
$options.="</option>";
$i++;
}
}
else
{
return "Table is empty";
}
return $options;
}
?>
How to use
Let’s consider example given in introduction
Following will be the table structure
Table Name – Categories
Field Names – CategoryID, CategoryName, CategoryDescription, IsEnabled
For creating combo of all categories
< select name = “categories”>
< ?
echo createCombo(“Categories”,”CategoryId”,”CategoryName”);
? >
< /select >
Using where condition
< select name = “categories”>
< ?
echo createCombo(“Categories”,”CategoryId”,”CategoryName”,0,”IsEnable=true”);
? >
< /select >
