Fill a combobox with php

[Anon]

Suppose you have 2 tables in a DB.
Catories with fields ID, Name
Items with ID, CatID and Name

During load of page, how to fill a combo/list-box with all rows found in table Categories ?

When it's done, and user want to look at other category, user just select it and 'submit with hidden value' so next reload of page will show all items for selected categori.

[Anon]

<?php

if($submit)
{

//connect to db
mysql_connect("localhost","root","");
mysql_select_db($db);

//query
$sql = mysql_query("SELECT * FROM Items WHERE CatID='$ID'");
while($row=mysql_fetch_row($sql))
{
$CatID = $row[0];
$Name = $row[1];
$Name = stripslashes($Name);

print ("$CatID | $Name<br>");
}

}

else
{
print ("form action='$PHP_SELF'>");
print ("<select name='category_id'>");

//connect to db
mysql_connect("localhost","root","");
mysql_select_db($db);

//query
$sql = mysql_query("SELECT * FROM Categories");
while($row=mysql_fetch_row($sql))
{
$ID = $row[0];
$Name = $row[1];
$Name = stripslashes($Name);

print ("<option value='$ID'>$Name</option");
}
print ("</select>");
print ("<input type='submit' name='submit' value='go'></form>");
}

?>

hth
scott d~

[Anon]

Thanks a lot Scott !

[Anon]

So ya want to populate a select field?
In this case, a combobox (size=1).
Just a matter of sucking up all rows in
the query:

[ex.1]

$Query = "SELECT ID,FieldName FROM TableName";
$Results = mysql_query($Query,$DBLink);

if (mysql_num_rows($Results)) {
print '<SELECT NAME="MultiSel" SIZE=1>';
print "\n";
while ($row = mysql_fetch_object($Results)) {
print "<OPTION>$row->FieldName\n";
}
print "</SELECT>\n";
} else {
print "<FONT COLOR=RED>No Values!</FONT>\n";
}

If you want to have multiple selections, try changing the first print statement, add the MULTIPLE parameter and brackets to the SELECT field:

[ex.2]

print "<SELECT NAME=\"MultiSel[]\" MULTIPLE SIZE=5\n";

On the next page, you'll be able to access
the selections via an array, MultiSel:

[ex.3]

// Print out selections from form submission
for ($i = count($MultiSel)-1; $i >= 0; $i--)
{
print "$MultiSel[$i] <br>\n";
}

There are a few methods of restoring the
original select box. The first one (and easiest) is to add all the selections to
the top of the new SELECT box, then write a query to exclude those selections:

[ex.4]

// Add previous submissions to the SELECT box
// and generate exclusive query.

print "<SELECT NAME=\"MultiSel\" MULTIPLE SIZE=5\n";
$Query = "SELECT * FROM TableName";
if (count($MultiSel)) {
$Query .= "WHERE ";
for ($i = count($MultiSel)-1; $i >= 0; $i--)
{
print "<OPTION SELECTED>$MultiSel[$i]\n";
$Query .= "(FieldName != '$MultiSel[$i]') AND ";
}
// Remove trailing AND
$Query = substr($Query,0,-4);
}
while ($row = mysql_fetch_object($Results)) {
print "<OPTION>$row->FieldName\n";
}
print "</SELECT>\n";

That's the basics of it. Of course there
are a few optimizations, like checking on
the last routines to see if there are any DB
results or submissions at all. These are
simple to add so I'll leave them to anyone
else.

Hope it helps!

• Aaron Koehl
  [e-mail]