Creating drop down lists from MySQL data

Drop down lists (SELECT elements) are fairly common in HTML forms. They allow the user to select from a list rather than typing in the data making it easier and reducing typing errors.

To create a drop down in a form in HTML you list the possible choices as OPTIONS in a SELECT element. PHP can create this list from MySQL data. The confusion comes as usual with all the escaping of quotes but otherwise it is a fairly simple loop embedded in an HTML form.

The following code creates the form in HTML and starts PHP when it is needed:

            
<body>

    <form method="get" action="http://www.yourwebskills.com/files/examples/process.php">
        
        <select id="cd" name="cd">
        
            <?php
            
            $mysqlserver="localhost";
            $mysqlusername="test";
            $mysqlpassword="test";
            $link=mysql_connect(localhost, $mysqlusername, $mysqlpassword) or die ("Error connecting to mysql server: ".mysql_error());
            
            $dbname = 'test';
            mysql_select_db($dbname, $link) or die ("Error selecting specified database on mysql server: ".mysql_error());
            
            $cdquery="SELECT cdTitle FROM firsttable";
            $cdresult=mysql_query($cdquery) or die ("Query to get data from firsttable failed: ".mysql_error());
            
            while ($cdrow=mysql_fetch_array($cdresult)) {
            $cdTitle=$cdrow["cdTitle"];
                echo "<option>
                    $cdTitle
                </option>";
            
            }
                
            ?>
    
        </select>
        
    </form>
    
</body>            
        

Paste this into a new PHP page, save as mysqldropdown.php and upload. It should be fairly clear how it works but look again at that page which explained some of the fields which can be used in an HTML form if you need to.

The form uses the get method because that is what your processing form (formupdate2.php) uses. You will be using that page soon.

Purpose?

The user can now choose a CD from the list in the drop down.

You already saw how you can create links for the user to edit or delete data. This drop down list could be used as another way for the user to select a CD for further action. At the moment it is not complete but you are about to fix that.

Completing the process

At the moment the user chooses a cdTitle which is good for the user but not the database. The database needs to know the cdReference as that is the primary key. This is not a problem as HTML has a way to display one thing to the user but send another with the form:

 
<option value="23">
    Making Movies
</option>";
        

The VALUE attribute of the form field holds the CD reference for each entry in the drop down. It is the VALUE which is sent when the form is submitted.

This is how you show the user the CD title and artist but send the cdReference:

 
while ($cdrow=mysql_fetch_array($cdresult)) {
$cdTitle=$cdrow["cdTitle"];
$cdArtist=$cdrow["cdArtist"];
$cdReference=$cdrow["cdReference"];
    echo "<option value=\"$cdReference\">
        $cdTitle $cdArtist
    </option>";
}
        

Paste that in to replace your existing WHILE loop. Also change the SELECT query to include the cdArtist and cdReference fields:

            
$cdquery="SELECT cdTitle, cdArtist, cdReference FROM firsttable";
        

Test this version of the form and you should see three things being sent in the $_GET array.

To finish you need a page to display the existing data for the chosen record in a form. You should have first done this on the page on updating database records with MySQL. You should have then altered the page for use with links created by PHP and using $_GET. If you still have this page (formupdate2.php) use it as it should work unchanged. Change the ACTION of the form with the drop down to point to formupdate2.php.

The main reason for using $_GET for the dropdown form is that it will not conflict with formupdate2.php which uses $_POST.

submit to reddit Delicious Tweet