DB dropdowns

Making the `select` tag from DB entities

The options we just saw were written into the HTML page. However, often the list of options comes from a database.

Let's see how we'd make a dropdown listing the comedians. It will look like this to users:

Output

As comedians are added and removed from the DB, the list will change automatically. W00t!

Our PHP makes HTML to make this list. Here's the HTML the PHP outputs.

<label>Who is the best comedian?<br>
<select name="best-comedian">
<option value="0">(Not given)</option>
<option value='1'>Aisling Bea</option>
<option value='9'>David Mitchell</option>
<option value='10'>Eddie Izzard</option>
<option value='11'>Frankie Boyle</option>
<option value='5'>Isy Suttie</option>
<option value='7'>Jimmy Carr</option>
<option value='8'>Joe Wilkinson</option>
<option value='2'>Paula Poundstone</option>
<option value='4'>Roisin Conaty</option>
<option value='12'>Russel Howard</option>
<option value='3'>Sara Pascoe</option>
<option value='6'>Sarah Millican</option>
<option value='15'>Trevor Noah</option>
</select>
</label>

Let's check the PHP that makes this HTML.

<?php
// Connect to the database.
require_once 'library/db-connect.php';
// Load comedians.
$sql = "
SELECT *
FROM comedians
ORDER BY name;";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$stmnt->execute();
$comedianRows = $stmnt->fetchAll();
?><!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Best comedian</title>
<link rel="stylesheet" href="library/styles.css">
</head>
<body>
<h1>Best comedian</h1>
<form method="post">
<p>
<label>Who is the best comedian?<br>
<select name="best-comedian">
<option value="0">(Not given)</option>
<?php
foreach ($comedianRows as $comedianRow) {
$comedianId = $comedianRow['comedian_id'];
$comedianName = $comedianRow['name'];
print "<option value='$comedianId'>$comedianName</option>\n";
}
?>
</select>
</label>
</p>
</form>
</body>
</html>

The SQL puts all the comedians into the array $comedianRows (line 12):

$comedianRows = $stmnt->fetchAll();

Then we loop over the rows, making an option for each one:

<select name="best-comedian">
<option value="0">(Not given)</option>
<?php
foreach ($comedianRows as $comedianRow) {
$comedianId = $comedianRow['comedian_id'];
$comedianName = $comedianRow['name'];
print "<option value='$comedianId'>$comedianName</option>\n";
}
?>
</select>

Georgina

Cool!

Aye, it is.

Best comedian: add versus edit

When adding a record, the best comedian won't have anything selected:

Nothing selected for add

But when editing a record, there will be an existing value.

Comedian selected for edit

BTW, Aisling is a fave at the Mathiesons.

You can show the existing value, by adding adding checked to the right option, as your code makes the HTML.

Here's some code to add to the above for that. Assume $bestComedianId has an entity's existing selection.

<?php
foreach ($comedianRows as $comedianRow) {
$comedianId = $comedianRow['comedian_id'];
$comedianName = $comedianRow['name'];
$selected = '';
if ($comedianId == $bestComedianId) {
$selected = 'selected';
}
print "<option value='$comedianId' $selected>$comedianName</option>\n";
}
?>

The variable $selected is always output (line 9). Each time through the loop, $selected is set to MT. When the option's id matches the current one for the entity, then $selected is set to to the text selected.

Up next

The comedians select list, with the current value selected, shows how we represent relationships on forms. Let's see more of that.

Making the select tag from DB entities

Best comedian: add versus edit

Up next

Making the `select` tag from DB entities