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 : 17005
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
Multiple pages of data from a text file
Categories : PHP, Filesystem
Simple Connection to mSQL with PHP
Categories : PHP, mSQL, Databases
PHP Classes And Objects: A Guide To Development
Categories : PHP, PHP Classes, Object Oriented, Beginner Guides
Managing a Simple Mailing List
Categories : PHP, MySQL, Email
More PHP, XML and XSL for Wireless Content
Categories : PHP, XML, XSL, Wireless
Watching The Web
Categories : PHP, Databases, MySQL, HTTP, MD5
How To add paging (Pagination) with PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, HTML and PHP
Counting - Creating a simple counter
Categories : PHP, MySQL, Beginner Guides, To PHP, To MySQL
Building XML Trees with PEAR's XML_Tree Class
Categories : PHP, Pear, XML
Setup and Install Apache with PHP4 as a Dynamic Module (DSO)
Categories : PHP, PHP Configuration, Apache
PHP 101 Part 11 of 15 : Sinfully Simple
Categories : PHP, SimpleXML, XML
Simple PHP Templates With PatTemplate
Categories : PHP, Templates
Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases
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 :)