×
Namespaces

Variants
Actions

PHP & JavaScript & MySQL - Dynamic Drop Down List

From Nokia Developer Wiki
Jump to: navigation, search
Article Metadata
Article
Created: Maveric (20 Jun 2011)
Last edited: hamishwillee (20 Oct 2011)

Contents

Introduction

In this article we will go thru an example where we have two drop down menus. In the example prepared, the user is expected to Submit the car manufacturer and model.

Prerequirements

- MYSQL and two tables created (see the Implementation)

- PHP 5.x installed on your local machine or remote server

- JavaScript support enabled on the web browser

- Code editor of your choise

- Web browser of your choise (JavaScript support enabled)

Implementation

The example will make use of MySQL database tables out of which the information will be fetched into the drop down lists. The drop down boxes will be linked so, that the list on the second box will be populated based on the selection in the first one. The required tables are described later in this article, you can use the SQL statements to insert the data into your own database. Tables in use are linked with field "manu_id", which here refers to "Manufacturer ID". The table data consists of four car manufacturers and some car models of them. For the first table we can just go ahead and fetch the data from the database, and have it listed. This will happen when the user opens the drop down box. The SQL query is straightforward, and unconditional.

For the second table the selected value in the first drop down box will be used for the key of search within a SQL statement. The results will be brought into the box list. We will check the presence of the variable and if it is, the SQL is modified to further add a condition in the where clause. The variable for the second drop down box query will be passed by submitting the form into itself in case any value is selected within the first list.

The onchange event will be used for the form submitting. The first table is populated in the first drop down list by taking directly from the table without any conditions in sql statement. In case of second drop down list the presence of variable having the value of category is checked and if the category number is present then the sql is changed to add one condition in where clause. The variable to second list query is passed by submitting the form to itself when any value is selected in the first drop down list. We will be using the "onchange" event of the select element to submit the form.

Example Code

In the example there will be one function in JavaScript and it's job is to take care handling the onchange event occurring in the first drop down list box. The function is put inside the <head> tag. The action will be that when the element of the first list box is selected, this would call the JavaScript function, which then will add the item selected into the query string. Then the page will be reloaded. This will give the variable needed for using the second drop down list.

Note: This file is saved with name "demodropdownbox.php". If you need to change the name, please edit the "self.location" variable to contain your file name.

$connect = mysql_connect("mapswidgetsusers.db.3618229.hostedresource.com","mapswidgetsusers","MtM2008yG5") or die ("Could not connect.");
mysql_select_db("mapswidgetsusers") or die ("Could not find database");
$dbname='mapwidgetsusers';
?>
<html>
<head>
<title>Demo Drop-down box</title>
<script language=JavaScript>
function reload(form)
{
var selected_value=form.man.options[form.man.options.selectedIndex].value;
self.location='demodropdownbox.php?man=' + selected_value ;
}
</script>
</head>
<body>
<?
@$man=$HTTP_GET_VARS['man'];
if(strlen($man) > 0 and !is_numeric($man)){
echo "INVALID DATA.";
exit;
}
//This fetches content from car MANUFACTURER table first to get the AUDI,BMW,PORSCHE, SAAB list
 
$manufacturer_query=mysql_query("SELECT DISTINCT category,manu_id FROM MANUFACTURER order by category");
 
if(isset($man) and strlen($man) > 0)
{
echo "Loaded: Manufacturers.";
 
// If the numeric value of "man" (selected manufacturer model) is greater than zero we are OK
// Then we will perform an SQL query to find all car models with that number.
$models_query=mysql_query("SELECT DISTINCT MODELS FROM MODELS where manu_id=$man order by MODELS");
echo "Loaded: Models by Manufacturer ID.";
}else
{$models_query=mysql_query("SELECT DISTINCT MODELS FROM MODELS order by MODELS");
echo "Loaded: Models.";
}
 
echo "<form method=post name=f1 action='demodropdownbox_ui.php'>";
echo "<select name='man' onchange=\"reload(this.form)\"><option value=''>Select car manufacturer</option>";
 
while($qresult_manufacturer = mysql_fetch_array($manufacturer_query))
{
if($qresult_manufacturer['manu_id']==@$man){echo "<option selected value='$qresult_manufacturer[manu_id]'>$qresult_manufacturer[category]</option>"."<BR>";}
else{echo "<option value='$qresult_manufacturer[manu_id]'>$qresult_manufacturer[category]</option>";}
}
echo "</select>";
 
echo "<select name='mod'><option value=''>Select car model</option>";
 
while($qresult_models = mysql_fetch_array($models_query))
{
echo "Loaded: Models queried.";
echo "<option value='$qresult_models[MODELS]'>$qresult_models[MODELS]</option>";
}
echo "</select>";
echo "<input type=submit value=Submit>";
echo "</form>";
?>
</body>

This file is saved with name "demodropdownbox_ui.php"

<?php
?>
<html>
<head>
<title>Demo Drop-Down Box</title>
</head>
<body>
<?
$man=$_POST['man'];
$mod=$_POST['mod'];
echo "Manufacturer is \$man = $man <br>Model is \$mod = $mod ";
?>
</body>
</html

Required SQL queries

CREATE TABLE MANUFACTURER(
   manu_id int( 2 ) NOT NULL AUTO_INCREMENT ,
   category varchar( 25 ) NOT NULL default ,
   PRIMARY KEY ( manu_id )
      ) TYPE = MYISAM ;


INSERT INTO MANUFACTURER
VALUES ( 1, 'AUDI' ) ;
INSERT INTO MANUFACTURER
VALUES ( 2, 'BMW' ) ;
INSERT INTO MANUFACTURER
VALUES ( 3, 'PORSCHE' ) ;
INSERT INTO MANUFACTURER
VALUES ( 4, 'SAAB' ) ;


CREATE TABLE MODELS(
      manu_id int( 2 ) NOT NULL default '0',
      MODELS varchar( 25 ) NOT NULL default 
     ) TYPE = MYISAM ;


INSERT INTO MODELS VALUES (1, 'A8'); 
INSERT INTO MODELS VALUES (1, 'Q7 '); 
INSERT INTO MODELS VALUES (1, 'TT'); 
INSERT INTO MODELS VALUES (1, 'R8'); 
INSERT INTO MODELS VALUES (2, '325'); 
INSERT INTO MODELS VALUES (2, 'Z3 Roadster'); 
INSERT INTO MODELS VALUES (2, 'E70 X5'); 
INSERT INTO MODELS VALUES (2, 'E63 M6'); 
INSERT INTO MODELS VALUES (3, 'Cayenne'); 
INSERT INTO MODELS VALUES (3, 'Boxter'); 
INSERT INTO MODELS VALUES (3, 'Carrera'); 
INSERT INTO MODELS VALUES (3, '911 GT3'); 
INSERT INTO MODELS VALUES (4, '9000 Turbo'); 
INSERT INTO MODELS VALUES (4, '9-3 Griffin'); 
INSERT INTO MODELS VALUES (4, '9-4X'); 
INSERT INTO MODELS VALUES (4, 'Turbo X'); 

Tested on

Mozilla Firefox 4.0.1

This page was last modified on 20 October 2011, at 01:15.
426 page views in the last 30 days.

Was this page helpful?

Your feedback about this content is important. Let us know what you think.

 

Thank you!

We appreciate your feedback.

×