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 : 17721
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
Create an LDAP Address Book with PHP
Categories : PHP, LDAP
How TO Install PHP, Apache and MySQL on Linux or Unix
Categories : PHP, MySQL, Apache, Installation, Beginner Guides
Parsing XML With DOMXML And PHP
Categories : XML, PHP
Serializing XML With PHP
Categories : PHP, XML, Serialize
Developing Custom PHP Extensions: Part 1
Categories : PHP, PHP Extensions
Installing PHP With IIS To Create A Discussion Forum Article URL
Categories : PHP, IIS, Web Servers
Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation
Building XML Web Services with PHP NuSOAP
Categories : PHP, NuSOAP, XML
Introduction to WAP using WML, ASP and PHP
Categories : PHP, WAP, WML
Web Development With PHP FastTemplate
Categories : PHP, Templates, FDF
Counting - Creating a GIF based counter using PHP and MySQL
Categories : Beginner Guides, PHP, To PHP, To MySQL, MySQL
Creating a Mail Form with PHP and Flash
Categories : PHP, Flash, Mail
PHP Date and Time Primer
Categories : PHP, Date/time
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 :)