This is an extremely simply tutorial on how to populate a drop down list using values that you’ve selected from MySQL. In this guide, I will be using PHP to retrieve the data and populate a SELECT element.
In the following example, we retrieve a list of users from MySQL using PHP and the PDO object. We then loop through the result set so that we can populate our dropdown SELECT element:
<?php
//Connect to our MySQL database using the PDO extension.
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
//Our select statement. This will retrieve the data that we want.
$sql = "SELECT id, name FROM users";
//Prepare the select statement.
$stmt = $pdo->prepare($sql);
//Execute the statement.
$stmt->execute();
//Retrieve the rows using fetchAll.
$users = $stmt->fetchAll();
?>
<select>
<?php foreach($users as $user): ?>
<option value="<?= $user['id']; ?>"><?= $user['name']; ?></option>
<?php endforeach; ?>
</select>
A quick overview of the code above:
- We connect to MySQL using the PDO object.
- We select rows from our users table using a prepared statement.
- We then loop through the results and print out the option values for our SELECT tag. In this case, we display the name of the user in the dropdown.
The resulting SELECT tag will look something like this:
<select>
<option value="1">Joe Bloggs</option>
<option value="2">Sarah Bloggs</option>
<option value="3">Jane Doe</option>
</select>
Note: When populating SELECT tags with database rows, it is a good idea to insert the primary key value into the value attribute of each <option> tag. This means that the option value will contain the unique primary key of the row in question. This was required in the example above, simply because two users may have the exact same name.