Fetching data from MySQL database to HTML dropdown list

Question

I have a web site that contains an HTML form, in this form I have a dropdownlist with list of agents that works in the company, I want to fetch data from MySQL database to this dropdownlist so when you add a new agent his name will appear as an option in the drop down list.

<select name="agent" id="agent">
</select>  

Answer 1

To do this you want to loop through each row of your query results and use this info for each of your drop down's options. You should be able to adjust the code below fairly easily to meet your needs.

// Assume $db is a PDO object
$query = $db->query("YOUR QUERY HERE"); // Run your query

echo '<select name="DROP DOWN NAME">'; // Open your drop down box

// Loop through the query results, outputing the options one by one
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
   echo '<option value="'.htmlspecialchars($row['something']).'">'.htmlspecialchars($row['something']).'</option>';
}

echo '</select>';// Close your drop down box

Answer 2

# here database details      
mysql_connect('hostname', 'username', 'password');
mysql_select_db('database-name');

$sql = "SELECT username FROM userregistraton";
$result = mysql_query($sql);

echo "<select name='username'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['username'] ."'>" . $row['username'] ."</option>";
}
echo "</select>";

# here username is the column of my table(userregistration)
# it works perfectly

Answer 3

You need to fetch all rows from the database and then iterate over them, displaying a new <option> for each one of them. Pay attention to avoid XSS by using htmlspecialchars().

$pdo = new \PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

// Select all values from the table Agents
$stmt = $pdo->prepare("SELECT Id, Name FROM Agents");
$stmt->execute();

echo '<select name="agent" id="agent">';
// For each row from the DB display a new <option>
foreach ($stmt as $row) {
    // value attribute is optional if the value and the text is the same
    echo '<option value="'.htmlspecialchars($row['Id']).'">';
    echo htmlspecialchars($row['Name']); // The text to be displayed to the user
    echo '</option>';
}
echo '</select>';

If you want to preselect one of the values, then you need to apply selected attribute to one of the <options>:

$selected = 'Somebody';

echo '<select name="agent" id="agent">';
foreach ($stmt as $row) {
    if ($selected === $row['Name']) {
        echo '<option value="'.htmlspecialchars($row['Id']).'" selected >';
    } else {
        echo '<option value="'.htmlspecialchars($row['Id']).'">';
    }
    echo htmlspecialchars($row['Name']);
    echo '</option>';
}
echo '</select>';

Answer 4

What you are asking is pretty straight forward

1. execute query against your db to get resultset or use API to get the resultset

2. loop through the resultset or simply the result using php

3. In each iteration simply format the output as an element

the following refernce should help

HTML option tag

Getting Datafrom MySQL database

hope this helps :)