WeberDev.com PHP and MySQL Code

LOG IN
BEGINNER GUIDESPHP CLASSESCODE SEARCHARTICLES SEARCHPHP FORUMSPHP MANUALPHP FUNCTIONS LISTWEB SITE TEMPLATES
Start typing to search for PHP and MySQL Code Snippets and Articles Search


Submit a code Example / Snippet Join us on FaceBook
Submit a code Example / Snippet Submit Your Code
Search Engine Optimization Monitor SEO Monitor
Web Site UpTime Monitor UpTime Monitor
Your Personal Examples List My Favorite Examples
Your Personal Articles List My Favorite Articles
Edit Account Info Update Your Profile
PHP Code Search
Web Development Forums
Learn MySQL Playing Trivia
PHPBB2 Templates
Web Development Resources
Web Development Content
PHPClasses
PHP Editor
PHP Jobs
Vision.To Design
Ajax Tutorials
PHP Programming Help
PHP/MySQL Programming
Webmaster Resources
Webmaster Forum
XML meta language
website builder



Go Back Add a Comment Send this Article to a friend Add this Article to your personal favoritest for easy future access to your favorite Code Examples and Articles. Submit a code example Print this code example.
BACK ADD A COMMENT SEND TO A FRIEND ADD TO MY FAVORITES SUBMIT AN ARTICLE PRINT
Title : Extracting Elements from a Database into a Select Form Field
Categories : PHP, HTML Picture not available
Meloni Julie
Date : 2000-04-24
Grade : 0 of 5 (graded 0 times)
Viewed : 18086
Search : More Articles by Meloni Julie
Action : Grade This Article
Tools : My Favotite Articles


Submit your own code examples 
 


A common question on the PHP mailing list is "How do I populate a select list with items in a database?". Thank the maker for the while construct, because it makes this all very easy!



In Chapter 7, "Advanced PHP Techniques: Web-Based Database Adminstration" of PHP Essentials, I use very verbose coding techniques to match, then print, an <OPTION> field within a <SELECT> form element (drop-down list).



I'm just going to dive right in here, and show you how to use a while loop to get a list from a database table. If you are having trouble following the concept, blame it on me for now explaining it thoroughly, and contact me to help fill in the gaps.



Suppose you have a database table called OPERATING_SYSTEMS, which contains one field (OS) and looks something like this:




+-------------------+
| OS |
+-------------------+
| Win 95/98 |
| Linux (SuSE) |
| Linux (Red Hat) |
| Win NT |
| Linux (Mandrake) |
| Solaris |
| Linux (Debian) |
| Win 2000 |
| FreeBSD |
| Linux (Other) |
| Mac OS |
| Irix |
| Linux (Caldera) |
| Linux (Slackware) |
+-------------------+


The goal is to bring the contents of the OPERATING_SYSTEMS table into some sort of form you're making, so you would do the following with MySQL (you can substitute any database connectivity code that you would normally use).



First, start an HTML shell and add your basic database connectivity code, including the SQL statement:


<HTML>
<HEAD>
<TITLE>Sample Form&lt;/TITLE>
&lt;/HEAD>
<BODY>


<?

// create connection
$connection = mysql_connect("servername","username","password")
or die(
"Couldn't make connection.");

// select database
$db = mysql_select_db("myDB", $connection)
or die(
"Couldn't select database.");


// create SQL statement
$sql = "SELECT OS FROM OPERATING_SYSTEMS
ORDER BY OS ASC"
;

// execute SQL query and get result
$sql_result = mysql_query($sql,$connection)
or die(
"Couldn't execute query.");


?>


Next, since the values will be represented within the context of a form, pretend that you have a form. I've made a fake one, below. In a few minutes, we'll create a string called $option_block, which will contain all of the <OPTION> variables created by sucking data out of the MySQL table (OPERATING_SYSTEMS). That's why the echo statement appears in the code below.


<FORM method="[your method]" action="[your action]">

<P>Operating System:<br>
<SELECT name="operating_system">

<? echo "$option_block"; ?>

&lt;/SELECT>

<P><INPUT type="submit" value="submit">&lt;/p>


&lt;/FORM>

&lt;/BODY>
&lt;/HTML>


Now go back into your PHP code, and add the while loop that will get the list of operating systems and put them in <OPTION> elements. First, separate the results by row, using the mysql_fetch_array function:


while ($row = mysql_fetch_array($sql_result)) {

// more code here

}


The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (in this case, simply OS), create specific variables:


$os = $row["OS"];


Put the value inside an <OPTION> element. Remember, we'll be adding to the $option_block string as the loop continues, so use the concatenation operator (.=) instead of the assignment operator:


$option_block .= "<OPTION value=\"$os\">$os&lt;/OPTION>";


The entire while loop looks like this:


while ($row = mysql_fetch_array($sql_result)) {

$os = $row["OS"];
$option_block .= "<OPTION value=\"$os\">$os&lt;/OPTION>";
}


From top to bottom, this code looks something like this:





<HTML>
<HEAD>
<TITLE>Sample Form&lt;/TITLE>
&lt;/HEAD>
<BODY>


<?

// create connection
$connection = mysql_connect("servername","username","password")
or die(
"Couldn't make connection.");

// select database
$db = mysql_select_db("myDB", $connection)
or die(
"Couldn't select database.");


// create SQL statement
$sql = "SELECT OS FROM OPERATING_SYSTEMS
ORDER BY OS ASC"
;

// execute SQL query and get result
$sql_result = mysql_query($sql,$connection)
or die(
"Couldn't execute query.");

// put data into drop-down list box
while ($row = mysql_fetch_array($sql_result)) {

$os = $row["OS"];
$option_block .= "<OPTION value=\"$os\">$os&lt;/OPTION>";
}

?>

<FORM method="[your method]" action="[your action]">

<P>Operating System:<br>
<SELECT name="operating_system">

<? echo "$option_block"; ?>

&lt;/SELECT>

<P><INPUT type="submit" value="submit">&lt;/p>


&lt;/FORM>

&lt;/BODY>
&lt;/HTML>


Using the code above, the loop will print a drop-down list box and submit button like the following:





Suppose, however, that this form was part of a "Modify Record" script, where you were pulling a record's values from a database table, and pre-populating a form with the values of that record. One simple modification to the above code will help you to pre-select an item in the drop-down list box.



If the record contains a value called $curr_os, and you want to pre-select an item in the drop-down list box based on that values, simply re-write the while loop like this:


while ($row = mysql_fetch_array($sql_result)) {

$os = $row["OS"];

if ($curr_os == $os) {

$option_block .= "<OPTION value=\"$os\" selected>$os&lt;/OPTION>";

} else {

$option_block .= "<OPTION value=\"$os\">$os&lt;/OPTION>";

}
}


Using this new code, and supposing that the value of $curr_os was "Solaris", the loop will print a drop-down list box and submit button like the following:











Webstatistics with Redirectors
Categories : PHP, HTML, HTML and PHP
tracking where and what on your site people are clicking
Categories : PHP, MySQL, HTML and PHP, HTML
Simple PHP Templates With PatTemplate
Categories : PHP, Templates
Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases
Protecting PHP Scripts with HTTP Authorization
Categories : PHP, HTTP, Security, Authentication
Honey, I Shrunk My Website
Categories : PHP, PHP options/info, Site Planning, Other
PHP 101 Part 8 of 15 : Databases and Other Animals
Categories : PHP, Beginner Guides, Databases
PHP And Regular Expressions 101
Categories : PHP, Regexps
String Theory - A discussion of PHP string function
Categories : PHP, Strings
Simple Connection to Oracle with PHP
Categories : PHP, Oracle, Databases
Making PHP Forms Object-Oriented
Categories : PHP, HTML and PHP, Object Oriented
Custom Error Messages When Using $PHP_SELF as Form Action
Categories : PHP, PHP Configuration
Introduction to using PHP 5.0 and SQLite
Categories : PHP, SQLite, Databases
An Alternative to Perl: Shell Scripting With PHP
Categories : PHP, Shell Scripting
Beginners Guide to PHP - Introduction to cookies
Categories : Beginner Guides, Cookies, To PHP, PHP
Anonymous wrote : 30
I have tried this solution in a little bit different way, but i
dont get it to work. Im going to use the "output" of the
<select>, to insert to a different table in the same
database. Which means that i have two queries in the
same script, one "insert into table1..! and one select *
from table2.." How can i combine these two, so i can
use info from some tables, and then put the info into a
different table, with the drop down box?
Anonymous wrote : 76
For a long time now i have tried to get this example to
work. With a few minor changes i got it to work :) Here
is what i did:

// $os = $row["OS"]; and altered
$option_block .= "<OPTION
value=\"$os\">$os</OPTION>";
to $option_block .= "<OPTION value=`".$row
[0]."`>".$row[0]."</OPTION>";

Now it works - I cant say whats wrong in the example,
but this works for me :)