Jump to content

Table Data into Drop Down


neobolt
Go to solution Solved by codebyren,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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.

 

Link to comment
Share on other sites

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');
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.