Table Data into Drop Down

[neobolt]

Is there a better way to load table values into drop down menus?

Basically I have 3 different drop down options which need to have data loaded into them from the same table in a database.

Here is what I have so far. It works but I feel like there must be a better way to make this happen.
 

<?php
 include("connect.php");
mysql_connect("$server", "$username", "$password") or die(mysql_error());
mysql_select_db("$database") or die(mysql_error());

// HEADER INCLUDE
include("header.php");
	echo "<br/><br/>";
	echo "<form action='add-record2.php' method='post'>";


// GET ALL DATA FOR "LOCATION" FROM "ADMIN" TABLE
$location = mysql_query("SELECT Location FROM admin") 
or die(mysql_error());
	echo "Location:";
	echo "<select name='location'>";
while($row = mysql_fetch_array( $location )) {
	echo "<option value='". $row['Location'] ."'>"; 
	echo $row['Location'];
	echo "</option>"; 
} 
echo "</select><br/>
Task Description: <textarea rows='4' cols='50' name='task'></textarea><br/>
Responsibility: <select name='responsibility'>";


// GET ALL DATA FOR "RESPONSIBILITY" FROM "ADMIN" TABLE
$responsibility = mysql_query("SELECT Responsibility FROM admin") 
or die(mysql_error());
while($row = mysql_fetch_array( $responsibility )) {
	echo "<option value='". $row['Responsibility'] ."'>"; 
	echo $row['Responsibility'];
	echo "</option>"; 
} 
echo "</select><br/>
Type: <select name='type'>";


// GET ALL DATA FOR "TYPE" FROM "ADMIN" TABLE
$type = mysql_query("SELECT Type FROM admin") 
or die(mysql_error());
while($row = mysql_fetch_array( $type )) {
	echo "<option value='". $row['Type'] ."'>"; 
	echo $row['Type'];
	echo "</option>"; 
} 
echo "</select><br/>
Frequency(days): <input type='text' name='frequency'>
<input type='submit'>
</form>"; // END OF FORM

?>

Thanks
John

[codebyren]

There is a lot of repeated code for dealing with DB queries.  You could try something like this:

 

<?php
// Fetch locations, responsibilities and types in one go...
$locations = $responsibilities = $types = array();
$query = "SELECT `Location`, `Responsibility`, `Type` FROM `admin`";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    $locations[] = $row['Location'];
    $responsibilities[] = $row['Responsibility'];
    $types[] = $row['Type'];
}

// Maybe hack in some last-minute sorting...
sort($locations);
sort($responsibilities);
sort($types);
?>

<!-- A sample drop-down menu population -->
<label for="location">Location: </label>
<select name="location" id="location">
    <option value="">Please Select</option>
    <?php foreach ($locations as $location) : ?>
    <option value="<?php echo $location; ?>"><?php echo $location; ?></option>
    <?php endforeach; ?>
</select>

It's a personal preference I guess, but that looks pretty clean.

 

[akphidelt2007]

You can create a function that returns the select string back to you and all you do is feed the array and array key. Here is an untested example

 

 

function buildSelect($name,$query,$valueKey,$textKey=false)
{
//if the value key = the text key then just keep them the same
$textKey = $textKey ? $textKey : $valueKey;

$html = "<select name='$name'>";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
      $html .= "<option value='{$row[$valueKey]}'>{$row[$textKey]}</option>";
}
 
$html .= "</select>";
 
return $html;
}

 

So then for your code you would do something like this...

 

echo "Location:";
echo buildSelect('location',"SELECT Location FROM admin",'Location');

[neobolt]

Thanks guys for the responses.

I got behicthebuilder's solution to work quite well.

That's not to say it's the better solution, just for my little knowledge of php I understood this solution better. lol

Thanks

John