loading values from database into a select box

markwin

im just wondering how i would load values from a database to a select field in a basic html form?

i cant seem to find any info on this

[deleted]

<?
mysql_connect('localhost','user','pass');
mysql_select_db('dbname');
$result = mysql_query("select * from table");
echo "<SELECT name=\"nameofselect\">\n";
while($row = mysql_fetch_assoc($result)) {
	echo "<OPTION value=\"{$row['somefield']}\">{$row['someotherfield']}</OPTION>\n";
}
echo "</SELECT>\n";
?>

markwin

do i have to write anything in the somefields. i have the select box coming up now, but there are no values coming in. it has ten spaces but they are just empty. ten is the number of records, but they are just blank

[deleted]

somefield and someotherfield should be replaced by whatever your field names are in the MySQL table...

examancer

Originally posted by markwin
do i have to write anything in the somefields. i have the select box coming up now, but there are no values coming in. it has ten spaces but they are just empty. ten is the number of records, but they are just blank

'somefield' should be replaced with a column name, while 'someotherfield' should be replaced with some other column name.

here is an example where two columns are being pulled out of a database, one is an catagory id number, and the other is the name of the catagory. Remember, this is just an example to show you how it works, this code probably won't work if you just copy and paste it, you have to make your own around your own database.

$sql = "SELECT catagory_id, catagory_name FROM catagories";
$q = mysql_query($sql);

echo ("<SELECT name=\"catagories\">\n");

// $catagory is an array, and each key in the array is the name of a column.
// stored under each key is the value for that column in the database.
while ($catagory = mysql_fetch_array($q)) {
  echo ("<option name=\"" . $catagory['catagory_id'] . "\">" . $catagory['catagory_name'] . "</option>\n");
}

echo ("</select>");

not much different than the first example, but hopefully you can see where your column names would go and how it all works.

markwin

great it worked. thanks man. cheers

techniq

You can also do this if you are using ADOdb as your database library. It uses a built-in function within ADOdb.

<?php

$dbconn->Connect($db_server, $db_user, $db_pass, $db);
$sSQL = 'SELECT catagory_id, catagory_name FROM catagories';
$q = $dbconn->Execute($sSQL);
print $q->GetMenu('catagory_id', 'catagory_name');
$dbconn->Close();

?>

GetMenu() Function overview:

GetMenu($name, [$default_str=''], [$blank1stItem=true], [$multiple_select=false], [$size=0], [$moreAttr=''])

Generate a HTML menu (<select><option><option></select>). The first column of the recordset (fields[0]) will hold the string to display in the option tags. If the recordset has more than 1 column, the second column (fields[1]) is the value to send back to the web server.. The menu will be given the name $name.

If $default_str is defined, then if $default_str == fields[0], that field is selected. If $blank1stItem is true, the first option is empty. $Default_str can be array for a multiple select listbox.

To get a listbox, set the $size to a non-zero value (or pass $default_str as an array). If $multiple_select is true then a listbox will be generated with $size items (or if $size==0, then 5 items) visible, and we will return an array to a server. Lastly use $moreAttr to add additional attributes such as javascript or styles.

Menu Example 1: GetMenu('menu1','A',true) will generate a menu: ABCfor the data (A,1), (B,2), (C,3). Also see example 5.

Menu Example 2: For the same data, GetMenu('menu1',array('A','B'),false) will generate a menu with both A and B selected:
ABC

GetMenu2($name, [$default_str=''], [$blank1stItem=true], [$multiple_select=false], [$size=0], [$moreAttr=''])

This is nearly identical to GetMenu, except that the $default_str is matched to fields[1] (the option values).

Menu Example 3: Given the data in menu example 2, GetMenu2('menu1',array('1','2'),false) will generate a menu with both A and B selected in menu example 2, but this time the selection is based on the 2nd column, which holds the values to return to the Web server.