Populating a Drop-down List from a Database

In many cases, it is sensible to hard-code a set of drop-down list options right in the HTML portion of a page as was done in the previous example. However, when the contents of a list are frequently changing, such an approach would require a lot of needless page updating. Just as you can use PHP to retrieve data to be overlaid on a map, you can also use it to dynamically populate a drop-down list.

To illustrate one approach to this problem, let's look at a slightly modified version of the Jen and Barry's page from earlier in the lesson.

It looks and behaves the same to the end user. However, this version pulls the names of the cities from MySQL.  Note that in place of the select element that had been hard-coded before, there is now simply an empty div (with an id assigned). The other difference in this version can be found at the beginning of the initMap() function. There before the initialization of the map is a jQuery ajax() call. This code reads the output of a script called get_city_list.php and inserts that text into the empty placeholder div.  Here is the source code of get_city_list.php:

https://phpdb.aset.psu.edu/phpjed124/get_city_list.php

<?php

$link = mysqli_connect("instructdb3.ait.psu.edu","inst_4","password","jed124");

$sql = "SELECT name FROM candidate_cities GROUP BY name;";

$result = mysqli_query($link,$sql);
if ($result != 0) {
    echo '<label>City:';
    echo '<select name="city">';
    echo '<option value="">all</option>';

    $num_results = mysqli_num_rows($result);
    for ($i=0;$i<$num_results;$i++) {
        $row = mysqli_fetch_array($result);
        $name = $row['name'];
        echo '<option value="' .$name. '">' .$name. '</option>';
    }

    echo '</select>';
    echo '</label>';
}

mysqli_close($link);

?>

The logic behind this script is rather straightforward. It first outputs the start tags for the list's label and select elements, followed by the "all" option (for displaying all the cities). It then loops through the records returned by a query that selects a unique list of cities. The query's GROUP BY clause wasn't really necessary for this table, but I included it because such a clause is often useful for eliminating duplicates. For example, imagine developing a real estate application that enabled users to select a school district from a list and see all of the available properties in that district. You would want each school district to show up in the drop-down list only once, but the table holding the property listings is likely to contain multiple occurrences of each district. A GROUP BY on the school district field would yield a list without duplicates (and sorted alphabetically).

This concludes the content on relational databases and their use in a web-mapping context.  On the next page you'll find a project scenario that you can complete as a way of putting what you've learned here into practice and/or to earn extra credit.