Dynamic Drop Down with PHP and mysqli

When you have a database make it work for you. This example shows how to use a database to create a simple HTML drop down list.

Assume we have a table such as:

Film Name Film Certificate
Tropic Thunder 15
TRON: Legacy PG
Inception 12
The Tourist 12
The Shawshank Redemption 15
The Usual Suspects 18
In Bruges 18
Hurt Locker 15
L.A. Confidential 18

Using SQL DISTINCT we could acquire a list of the film certificates that feature in the data.

SELECT DISTINCT filmCertificate FROM movies ORDER BY filmCertificate

This would produce.


From this data we would like to produce a drop down as follows:

<select name="filmCert">
<option value="12">12</option>
<option value="15">15</option>
<option value="18">18</option>
<option value="PG">PG</option>
<option value="U">U</option>

Tip: See this page for the a revision of how the HTML select and option are used to create a drop-down.

Now for the PHP. First I'll include a connection to the database.

$hostname = "myserver.com";
$user = "mylogin";
$password = "mypassword";
$connection = mysqli_connect($hostname, $user, $password);

With the above saved in an 'includes' folder as 'conn.inc.php' we can now write our SQL.

$queryFilmCerts = "SELECT DISTINCT filmCertificate FROM movies ORDER BY filmCertificate";
$resultFilmCerts = $mysqli->query($queryFilmCerts);

With the results now stored in $resultFilmCerts we can loop the results and echo a HTML <option> tag.

        <select name="filmCert">
			while ($rowCerts = $resultFilmCerts->fetch_assoc()) {
				echo "<option value=\"{$rowCerts['filmCertificate']}\">";
				echo $rowCerts['filmCertificate'];
				echo "</option>";

View Demo