Jump to: navigation, search

PHP Generate Dynamic Dropdown List

From w3cyberlearnings

Contents

Requirement

  • MySQL Database
  • Web Server: Apache, IIS
  • PHP5
  • ADODB Driver

Objective

  • Demonstrate you how to generate dynamic drop down list.
  • How to generate drop downlist using jquery ajax.

Create Table

CREATE TABLE `myorder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(200) NOT NULL,
  `customer_name` varchar(200) NOT NULL,
  `address` varchar(200) NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`)
)

Sample Records

INSERT INTO `myorder` 
     VALUES 
(1,'Java','Bob Maat','2450 Jame Dr, Houston,Tx',30),                             
(2,'PHP/MySQL','Paul Chao','3400 Lake Rd, Houston,Tx',40),
(3,'MySQL/Ajax','David','3203 Maka, Houston, Tx',39),
(4,'MySQL 5','Paul David','3203 Franklin Dr, Houston, Tx',39),
(5,'Postgresql','Lili','430 Mimin, Ruston, LA',32),
(6,'PHP/Postgresql','Jamey','7320 CA LA, LA, CA',30),
(7,'Perl','Chris d','3203 kilo, Houston, Tx',49),
(8,'jQuery','Maki','3320, Houston, Tx',20),
(9,'HTML/jQuery','Likinu','320, CA, FL',22),
(10,'PHP/jQuery','Maki','3320, Houston, Tx',25),
(11,'Mobile/jQuery','Maki','778, Houston, Tx',23),
(12,'CSS/jQuery','Maki','3320, Houston, Tx',42),
(13,'C++','LiMaki','3320, Houston, Tx',50),
(14,'java 5','LOMaki','3320, Houston, Tx',20),
(15,'Jscript','Maki','3320, Houston, Tx',40),
(16,'Linux','Maki','3320, Houston, Tx',30),
(17,'Mobile Design','Maki','3320, Houston, Tx',42),
(18,'Android/AJax','Maki','3320, Houston, Tx',60),
(19,'Webtop/EMCS','Maki','3320, Houston, Tx',70),
(20,'Documentum/Xcp','Maki','3320, Houston, Tx',120),
(21,'Javascript/JQuery','Maki','3320, Houston, Tx',20);

Select Records

mysql> select * from myorder;
+----+-------------------+---------------+-------------------------------+-------+
| id | item              | customer_name | address                       | price |
+----+-------------------+---------------+-------------------------------+-------+
|  1 | Java              | Bob Maat      | 2450 Jame Dr, Houston,Tx      |    30 |
|  2 | PHP/MySQL         | Paul Chao     | 3400 Lake Rd, Houston,Tx      |    40 |
|  3 | MySQL/Ajax        | David         | 3203 Maka, Houston, Tx        |    39 |
|  4 | MySQL 5           | Paul David    | 3203 Franklin Dr, Houston, Tx |    39 |
|  5 | Postgresql        | Lili          | 430 Mimin, Ruston, LA         |    32 |
|  6 | PHP/Postgresql    | Jamey         | 7320 CA LA, LA, CA            |    30 |
|  7 | Perl              | Chris d       | 3203 kilo, Houston, Tx        |    49 |
|  8 | jQuery            | Maki          | 3320, Houston, Tx             |    20 |
|  9 | HTML/jQuery       | Likinu        | 320, CA, FL                   |    22 |
| 10 | PHP/jQuery        | Maki          | 3320, Houston, Tx             |    25 |
| 11 | Mobile/jQuery     | Maki          | 778, Houston, Tx              |    23 |
| 12 | CSS/jQuery        | Maki          | 3320, Houston, Tx             |    42 |
| 13 | C++               | LiMaki        | 3320, Houston, Tx             |    50 |
| 14 | java 5            | LOMaki        | 3320, Houston, Tx             |    20 |
| 15 | Jscript           | Maki          | 3320, Houston, Tx             |    40 |
| 16 | Linux             | Maki          | 3320, Houston, Tx             |    30 |
| 17 | Mobile Design     | Maki          | 3320, Houston, Tx             |    42 |
| 18 | Android/AJax      | Maki          | 3320, Houston, Tx             |    60 |
| 19 | Webtop/EMCS       | Maki          | 3320, Houston, Tx             |    70 |
| 20 | Documentum/Xcp    | Maki          | 3320, Houston, Tx             |   120 |
| 21 | Javascript/JQuery | Maki          | 3320, Houston, Tx             |    20 |
+----+-------------------+---------------+-------------------------------+-------+
21 rows in set (0.00 sec)

Step 1: Connect to MySQL Use ADODB.inc.php

<?php


require_once ('adodb.inc.php');

// Connection string
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';

// set connection
$db = &ADONewConnection('mysql');
$db->PConnect($host, $user, $pass, $dbname);

...

Step 2: Pass value to the server

  • $size: Total of the select option size
  • $default_value: The default value
  • $type: select option's name and id name. 
// size of multiple drop down list
$size = $_REQUEST['size'];
// default value of drop downlist
$default_value = $_REQUEST['default_value'];
$type = $_REQUEST['type'];

Step 3: Query table and generate drop down list

if ($type && !empty($type)) {

    $sql_Query = "SELECT c.id, c.item as value FROM myorder c";

    $rs_options_obj =  $db->Execute($sql_Query);


    $multiple = "";
    if (!empty($size) && $size > 2) {
        $multiple = " size=\"{$size}\" multiple=\"multiple\"";
    }

    echo "<select name=\"{$type}\" id=\"{$type}\" {$multiple}>";
    echo '<option value="none" disabled="disable">Select...</option>';


    foreach ($rs_options_obj as $obj) {
        // default value
        if ($obj['id'] == $default_value && !empty($default_value)) {
            echo "<option value=\"{$obj['id']}\" selected=\"selected\">";
            echo $obj['value'];
            echo "</option>";
        } else { // no default value
            echo "<option value=\"{$obj['id']}\">";
            echo $obj['value'];
            echo "</option>";
        }
    }
    echo '</select>';
}
?>

Step 3-1: Query the table


    $sql_Query = "SELECT c.id, c.item as value FROM myorder c";

    $rs_options_obj = $rs = $db->Execute($sql_Query);

Step 3-2: Set Multiple and Size of the Select option

    $multiple = "";
    if (!empty($size) && $size > 2) {
        $multiple = " size=\"{$size}\" multiple=\"multiple\"";
    }

    echo "<select name=\"{$type}\" id=\"{$type}\" {$multiple}>";
    echo '<option value="none" disabled="disable">Select...</option>';

step 3-3: Generate the option

   foreach ($rs_options_obj as $obj) {
        // default value
        if ($obj['id'] == $default_value && !empty($default_value)) {
            echo "<option value=\"{$obj['id']}\" selected=\"selected\">";
            echo $obj['value'];
            echo "</option>";
        } else { // no default value
            echo "<option value=\"{$obj['id']}\">";
            echo $obj['value'];
            echo "</option>";
        }
    }
    echo '</select>';

Complete Code

<?php

require_once ('adodb.inc.php');

// Connection string
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';

// set connection
$db = &ADONewConnection('mysql');
$db->PConnect($host, $user, $pass, $dbname);


// size of multiple drop down list
$size = $_REQUEST['size'];
// default value of drop downlist
$default_value = $_REQUEST['default_value'];
$type = $_REQUEST['type'];


if ($type && !empty($type)) {

    $sql_Query = "SELECT c.id, c.item as value FROM myorder c";

    $rs_options_obj = $db->Execute($sql_Query);


    $multiple = "";
    if (!empty($size) && $size > 2) {
        $multiple = " size=\"{$size}\" multiple=\"multiple\"";
    }

    echo "<select name=\"{$type}\" id=\"{$type}\" {$multiple}>";
    echo '<option value="none" disabled="disable">Select...</option>';


    foreach ($rs_options_obj as $obj) {
        // default value
        if ($obj['id'] == $default_value && !empty($default_value)) {
            echo "<option value=\"{$obj['id']}\" selected=\"selected\">";
            echo $obj['value'];
            echo "</option>";
        } else { // no default value
            echo "<option value=\"{$obj['id']}\">";
            echo $obj['value'];
            echo "</option>";
        }
    }
    echo '</select>';
}
?>

Usage 1

http://localhost:80/dropdown.php?type=w3ccc
---------
<select name="okay" id="okay">
<option value="none" disabled="disable">Select...</option>
<option value="1">Java</option>
<option value="2">PHP/MySQL</option>
<option value="3">MySQL/Ajax</option>
<option value="4">MySQL 5</option>
<option value="5">Postgresql</option>
<option value="6">PHP/Postgresql</option>
<option value="7">Perl</option>
<option value="8">jQuery</option>
<option value="9">HTML/jQuery</option>
<option value="10">PHP/jQuery</option>
<option value="11">Mobile/jQuery</option>
<option value="12">CSS/jQuery</option>
<option value="13">C++</option>
<option value="14">java 5</option>
<option value="15">Jscript</option>
<option value="16">Linux</option>
<option value="17">Mobile Design</option>
<option value="18">Android/AJax</option>
<option value="19">Webtop/EMCS</option>
<option value="20">Documentum/Xcp</option>
<option value="21">Javascript/JQuery</option>
</select>

Usage 2

  • name=w3ccc, id=w3ccc 
http://localhost:80/dropdown.php?type=w3ccc&default_value=7&size=3
---------
<select name="w3ccc" id="w3ccc" size="3" multiple="multiple">
<option value="none" disabled="disable">Select...</option>
<option value="1">Java</option>
<option value="2">PHP/MySQL</option>
<option value="3">MySQL/Ajax</option>
<option value="4">MySQL 5</option>
<option value="5">Postgresql</option>
<option value="6">PHP/Postgresql</option>
<option value="7" selected="selected">Perl</option>
<option value="8">jQuery</option>
<option value="9">HTML/jQuery</option>
<option value="10">PHP/jQuery</option>
<option value="11">Mobile/jQuery</option>
<option value="12">CSS/jQuery</option>
<option value="13">C++</option>
<option value="14">java 5</option>
<option value="15">Jscript</option>
<option value="16">Linux</option>
<option value="17">Mobile Design</option>
<option value="18">Android/AJax</option>
<option value="19">Webtop/EMCS</option>
<option value="20">Documentum/Xcp</option>
<option value="21">Javascript/JQuery</option>
</select>

Usage 2: jQuery and Ajax

  • Using jquery to generate dynamic drop down list. 
<html>
    <head>
        <title>jQuery</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <script 
            type="text/javascript" 
            src="jquery.min.js">
        </script>
        <script type="text/javascript">
            $(document).ready(function(){
                $('#product').load('dropdown.php',
                  {type:'myspecialproduct',size:3,default_value:7});
            });
        </script>
    </head>
    <body>
        <form>
            Product:<br/>
            <span id="product"></span>
        </form>
    </body>
</html>

Related Links

Dynamic HTML From Array
  1. Dynamic Drop Down List with Array
  2. Dynamic List with Array
  3. Dynamic Radio with Array
  4. Dynamic Checkbox with Array
Dynamic HTML From Database
  1. Dynamic Drop Down List with Database
  2. Dynamic List from Database
  3. Dynamic Radio from Database
  4. Dynamic Checkbox from database
Dynamic HTML with Ajax
  1. Dynamic Drop Down List with Ajax
  2. Dynamic List with Ajax
  3. Dynamic Radio with Ajax
  4. Dynamic Checkbox with Ajax
  5. Dynamic Form with Ajax
Others Related
  1. Dynamic Drop down with default option
  2. How to retrieve Form Data
Retrieved from "http://www.w3cyberlearnings.com/index.php?title=PHP_Generate_Dynamic_Dropdown_List&oldid=3537"
Navigation
Web
SQL
MISC
References
Personal tools
Namespaces
Variants
Actions