Subscribe to PostsThis post concentrates on looking up values from a MySQL database table, and using them to populate a drop-down list, and pre-selecting them if an id number has been passed to the page when it was opened.
This is useful for categories, countries, salutations etc, basically, anything you would normally put in a drop-down list.
Let’s assume you have a very simple MySQL database table called categories. In this table, you have recorded a unique (auto-incrementing) id number, and the category name, for example:
1, Website Design
2, Cascading Stylesheets
3, Javascript
The first step is to set up your id look-up. Depending on whether you’re loading an existing record, or populating this from a link on your website, this can be achieved in different ways. However, let’s assume you’re passing the value through the link, eg: http://www.mywebsite.com/categories.php?cat_id=2
We now need to capture this variable, and in PHP this can be achieved with:
1 2 3 | <?php $cat_id=$_POST['cat_id']; ?> |
The next stage is to set up your select list in HTML:
1 2 3 4 | <select name="mylist"> <option value="">Please Select</option> <!-- INSERT LOOKED-UP VALUES HERE //--> </select> |
Having connected to your database through PHP (see this post), we can now replace the comment in the HTML above with:
1 2 3 4 5 6 7 8 9 10 11 12 13 | <?php $sql="select * from categories order by name asc"; $res = mysql_query($sql)or die('Could not fetch categories (SQL)'); while($row = mysql_fetch_array($res)) { $catID=$row['id']; $catName=$row['name']; if ($catID==$cat_id) { echo "<option value=\"$catID\" selected>$catName</option>\n"; } else { echo "<option value=\"$catID\">$catName</option>\n"; } } ?> |
This page, when loaded will provide you with a drop-down list, in alphabetical order, with the appropriate category selected, if the id number is found.
Leave a reply