JDT

John Dixon
Technology
Limited

Populate a Drop Down List Box from a MySQL Database Table

Quite often when you are developing web sites or applications it is necessary to create a drop down list box that contains entries from a database table. You can, of course, hard code the entries in the drop down list box, but this isn't really very elegant, and can create problems if the entries in the database table change. It is much better to extract the entries from the database and then populate the drop down list box using those entries.

Creating the database table

The following script, script.sql, could typically be used to create a database table (called 'years') that contains, for example, a list of years. These will be the entries that appear in the drop down list box.

script.sql

create table years
( yearID integer auto_increment,
year varchar(30),
primary key (yearID)
);

insert into years (yearID, year) values ('1', '2007-2008');
insert into years (yearID, year) values ('2', '2008-2009');
insert into years (yearID, year) values ('3', '2009-2010');
insert into years (yearID, year) values ('4', '2010-2011');
insert into years (yearID, year) values ('5', '2011-2012');
insert into years (yearID, year) values ('6', '2012-2013');

The following PHP script (script.php), which would form part of a form, will extract all the entries from the 'years' database table, and use them to populate the drop down list box.

script.php

<?php
$result = @mysql_query("select yearID,year from database_name.years");
print "<p>Select a year:\n";

print "<select name=\"yearID\">\n";

while ($row = mysql_fetch_assoc($result)){

    $yearID = $row['yearID'];
    $year = $row['year'];

    print "<option value=$yearID>$year\n";
    }

print "</select>\n";

print "</p>\n";
?>

If the entries in the database table change at some stage in the future, those changes will be automatically reflected in the drop down list box in the form.

Author: John Dixon
John Dixon Technology Ltd

Go back to MySQL Tutorials home page

Go back to Tutorials home page

Earnings Tracker is John Dixon Technology's FREE accounting / bookkeeping software tool.

Aimed at UK contractors and freelancers, Earnings Tracker enables you to perform bookkeeping and accounting tasks, helping you to keep track of your company's revenue and spending.

The software is written in PHP and MySQL and is available to use for FREE online, or as a FREE download.

Earnings Tracker can also be used simply as a dividend, corporation tax, or VAT calculator.

JDT

Privacy Statement

Terms & Conditions