Pulling Drop-down values from a database using Javascript and PHP
This tutorial will walk you through how to set drop-down boxes up to pull the values from a database as well as update the values of the other boxes based on what the user selects in the first and second boxes. This is handy if you are asking people to select categories of things they would like to search for, like tutorials. In this example we will be making a drop-down to select the group which will populate the category drop-down and then populate the sub-category drop-down based on which category is selected. We will be working with basic HTML, PHP, and MySQL as well as some basic JavaScript. To get started let’s set up our database. We will need to use three tables to achieve what we are doing. To start lets make our group table. The query to do so is following:
CREATE TABLE `group` ( `id` int(10) NOT NULL auto_increment, `group` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
This will create a table called group and set it to have the fields below:
Id – unique primary key, integer, auto-increments
Group – name of the various groups (programming, networking, ect..)
It will also set the auto increment value to 10)because we are going to insert 9 records to the table and we will give them the ids.
INSERT INTO `group` VALUES(1, 'Programming'); INSERT INTO `group` VALUES(2, 'Graphic Design'); INSERT INTO `group` VALUES(3, 'Networking'); INSERT INTO `group` VALUES(4, 'Office Software'); INSERT INTO `group` VALUES(5, 'Operating Systems'); INSERT INTO `group` VALUES(6, 'Other'); INSERT INTO `group` VALUES(7, '3D and Video'); INSERT INTO `group` VALUES(8, 'Web Development'); INSERT INTO `group` VALUES(9, 'Databases');
These queries will create the groups that we will be using to populate our first drop-down. Now we need to create our category table.
CREATE TABLE `category` ( `id` int(10) NOT NULL auto_increment, `group` varchar(100) NOT NULL default '', `category` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
This will create a table called category with the following fields:
Id – unique primary key, integer, auto-increments
Group – name of the various groups from the previous drop-down (programming, networking, ect..)
Category – name of the categories that will be listed underneath the corresponding groups
This one also sets our auto-increment value for us so that when we insert the values next, we will start with the right number.
INSERT INTO `category` VALUES(1, 'Graphic Design', 'Fireworks'); INSERT INTO `category` VALUES(2, 'Graphic Design', 'Flash'); INSERT INTO `category` VALUES(3, '3D and Video', '3d Studio Max'); INSERT INTO `category` VALUES(4, '3D and Video', 'AutoCAD'); INSERT INTO `category` VALUES(5, 'Web Development', 'HTML and XHTML'); INSERT INTO `category` VALUES(6, 'Web Development', 'CSS'); INSERT INTO `category` VALUES(7, 'Web Development', 'Other'); INSERT INTO `category` VALUES(8, 'Programming', 'C and C++'); INSERT INTO `category` VALUES(9, 'Programming', 'Java'); INSERT INTO `category` VALUES(10, 'Databases', 'MySQL'); INSERT INTO `category` VALUES(11, 'Databases', 'Oracle'); INSERT INTO `category` VALUES(12, 'Operating Systems', 'Windows XP'); INSERT INTO `category` VALUES(13, 'Operating Systems', 'Linux'); INSERT INTO `category` VALUES(14, 'Operating Systems', 'Mac OS'); INSERT INTO `category` VALUES(15, 'Business Apps', 'Excel'); INSERT INTO `category` VALUES(16, 'Business Apps', 'Powerpoint'); INSERT INTO `category` VALUES(17, 'Business Apps', 'Word'); INSERT INTO `category` VALUES(18, 'Other', 'Tips and Tricks'); INSERT INTO `category` VALUES(19, 'Other', 'Other'); INSERT INTO `category` VALUES(20, 'Networking', 'TCP IP'); INSERT INTO `category` VALUES(21, 'Networking', 'Cisco'); INSERT INTO `category` VALUES(22, 'Office Software', 'Peachtree'); INSERT INTO `category` VALUES(23, 'Office Software', 'QuickBooks');
As, you can see we are inserting at least two values for each one so that we can see this thing in action. Now we need to create our sub-category table. This one will have several entries in it.
CREATE TABLE `subcategory` ( `id` int(10) NOT NULL auto_increment, `category` varchar(100) NOT NULL default '', `subcategory` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 42;
This will create the following fields:
Id – unique primary key, integer, auto-increments
Category – name of the categories that will be listed underneath the corresponding groups that is related to those in the drop down.
Subcategory – The subcategories that belong to the corresponding category
Now we need to insert our data into this table:
INSERT INTO `subcategory` VALUES(1, 'Fireworks', 'Text Effects'); INSERT INTO `subcategory` VALUES(2, 'Fireworks', 'Other'); INSERT INTO `subcategory` VALUES(3, 'Flash', 'Animation'); INSERT INTO `subcategory` VALUES(4, 'Flash', 'Tweening'); INSERT INTO `subcategory` VALUES(5, '3d Studio Max', 'Animation'); INSERT INTO `subcategory` VALUES(6, '3d Studio Max', 'Effects'); INSERT INTO `subcategory` VALUES(7, 'AutoCAD', '3D'); INSERT INTO `subcategory` VALUES(8, 'AutoCAD', 'Architectural'); INSERT INTO `subcategory` VALUES(9, 'AutoCAD', 'Basics'); INSERT INTO `subcategory` VALUES(10, 'AutoCAD', 'Other'); INSERT INTO `subcategory` VALUES(11, 'CSS', 'General'); INSERT INTO `subcategory` VALUES(12, 'HTML and XHTML', '.htaccess'); INSERT INTO `subcategory` VALUES(13, 'HTML and XHTML', 'Advanced'); INSERT INTO `subcategory` VALUES(14, 'HTML and XHTML', 'Backgrounds'); INSERT INTO `subcategory` VALUES(15, 'Java', 'Applet Building'); INSERT INTO `subcategory` VALUES(16, 'Java', 'Application Building'); INSERT INTO `subcategory` VALUES(17, 'C and C++', 'Development'); INSERT INTO `subcategory` VALUES(18, 'C and C++', 'File Manipulation'); INSERT INTO `subcategory` VALUES(19, 'MySQL', 'General'); INSERT INTO `subcategory` VALUES(20, 'Oracle', 'General'); INSERT INTO `subcategory` VALUES(21, 'Linux', 'Administration'); INSERT INTO `subcategory` VALUES(22, 'Linux', 'Editing Files'); INSERT INTO `subcategory` VALUES(23, 'Windows XP', 'Administration'); INSERT INTO `subcategory` VALUES(24, 'Windows XP', 'Development'); INSERT INTO `subcategory` VALUES(25, 'Mac OS', 'General'); INSERT INTO `subcategory` VALUES(26, 'Mac OS', 'Networking'); INSERT INTO `subcategory` VALUES(27, 'Excel', 'General'); INSERT INTO `subcategory` VALUES(28, 'Powerpoint', 'General'); INSERT INTO `subcategory` VALUES(29, 'Word', 'General'); INSERT INTO `subcategory` VALUES(30, 'Networking', 'General'); INSERT INTO `subcategory` VALUES(31, 'Tips and Tricks', 'Hacks'); INSERT INTO `subcategory` VALUES(32, 'Tips and Tricks', 'OverClocking'); INSERT INTO `subcategory` VALUES(33, 'Other', 'BASH'); INSERT INTO `subcategory` VALUES(34, 'Other', 'MonitorTips'); INSERT INTO `subcategory` VALUES(35, 'TCP IP', 'General'); INSERT INTO `subcategory` VALUES(36, 'Cisco', 'Configuration'); INSERT INTO `subcategory` VALUES(37, 'Cisco', 'Programming'); INSERT INTO `subcategory` VALUES(38, 'Peachtree', 'Setup'); INSERT INTO `subcategory` VALUES(39, 'Peachtree', 'Configuration'); INSERT INTO `subcategory` VALUES(40, 'Quickbooks', 'Setup'); INSERT INTO `subcategory` VALUES(41, 'Quickbooks', 'Configuration');
Now our tables have been configured and we are ready to start writing the files to work with this database. First, we are going to write our connection string in separate file to allow for easy modification and also to be able to call the connection from any file by requiring it. Make a file and call it connection.php
<?php
$servername='localhost';
$dbusername='servername_dbname;
$dbpassword='password';
$dbname='database name;
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
?>
This simply sets the variable before running the connection string and allows the connection to carry to another file. Make sure you change you values in the first 4 declarations to the information needed to connect to your database. You can test the connection by going directly to that file. If you see nothing on the screen, the connection worked. If not, an error will show on the screen. This is the end of that file. Next we are going to create the html file that will actually display the form to the users – note that you could do this in the same file, but I felt for making it easy to understand, I would separate the files. I called this file index.html.
<html> <head> <title>Javascript Form Updater</title> <script language="javascript" src="update.php"></script> </head> <body bgcolor="#ffffff" onload="fillCategory();"> <FORM name="drop_list" action="success.php" method="POST" > <SELECT NAME="Main" onChange="SelectCat();" > <Option value="">Main</option> </SELECT> <SELECT id="Category" NAME="Category" onChange="SelectSubCat();"> <Option value="">Category</option> </SELECT> <SELECT id="SubCat" NAME="SubCat"> <Option value="">SubCat</option> </SELECT> </form> </body> </html>
This simply create the form in HTML and tells it were to get the javascript, which is in update.php. Note that we do not have a button to submit, you will need to add that and gather your POST variables on another page which is ( in the tutorial) success.php. Now we need to make update.php
<?php
require "connection.php";
echo "
function fillCategory(){
";
This first section simply calls the connection file to establish the DB connection and then beings the Jscript function. Notice that we are using php to echo the function.
$q1=mysql_query("select * from `group`");
echo mysql_error();
while($nt1=mysql_fetch_array($q1)){
echo "addOption(document.drop_list.Main, '$nt1[group]', '$nt1[group]');";
}
?>
}
Next, we create the query that will bring back the original results for the first drop-down box. We setup a while statement to keep adding the option to the drop-down box until there are no more to add. This is run every time the file is initially loaded. We do this using a javascript function that we will declare at the bottom of the page.
function SelectCat(){
removeAllOptions(document.drop_list.Category);
addOption(document.drop_list.Category, "", "Category", "");
<?
Next, we create the next function. This function is run every time the group drop-down is changed.
$q2=mysql_query("select distinct(`group`) from category");
while($nt2=mysql_fetch_array($q2)){
echo "if(document.drop_list.Main.value == '$nt2[group]'){";
$q3=mysql_query("select category from category where `group`='$nt2[group]'");
while($nt3=mysql_fetch_array($q3)){
echo "addOption(document.drop_list.Category,'$nt3[category]', '$nt3[category]');";
}
echo "}";
}
?>
}
This snippet selects each group from category only once (if it appears more than once, it will still only show it to me once). Then it checks to see if the value that it currently has is the value that is listed in the initial drop-down box. If it is, it runs a query on the category table to bring back all results that contains that group and adds them to the drop-down box for the categories.
function SelectSubCat(){
removeAllOptions(document.drop_list.SubCat);
addOption(document.drop_list.SubCat, "", "SubCat", "");
<?
$q4=mysql_query("select distinct(`category`) from subcategory");
while($nt4=mysql_fetch_array($q4)){
echo "if(document.drop_list.Category.value == '$nt4[category]'){";
$q5=mysql_query("select subcategory from subcategory where `category`='$nt4[category]'");
while($nt5=mysql_fetch_array($q5)){
echo "addOption(document.drop_list.SubCat,'$nt5[subcategory]', '$nt5[subcategory]');";
}
echo "}";
}
?>
}
This section does the same thing the last two sections did but changing the variables a little bit to populate the third drop-down on selection of the second.
function removeAllOptions(selectbox)
{
var i;
for(i=selectbox.options.length-1;i>=0;i--)
{
selectbox.remove(i);
}
}
function addOption(selectbox, value, text )
{
var optn = document.createElement("OPTION");
optn.text = text;
optn.value = value;
selectbox.options.add(optn);
}
[
Finally, we declare our two functions. The first one simply clears the drop-down boxes when it is called. The second one uses the JavaScript to add the option to the drop-down.
Now, if all three files are in the same directory you should be able to launch the html file and the first drop-down will be populated. Upon selection the second one will populate, and upon selection of that, the third one will. If it does not populate, go to the update.php file directly in your browser. You will get all the lines of JavaScript and you should see a PHP error on the page somewhere. You can use that error to trouble shoot your code. Please comment with questions and comments and thanks for reading.
Popularity: 20% [?]
Other Related Tutorials
on August 18th, 2008 at 12:51 pm
Hey, so far I haven’t figured it out yet. In my situation I really only need it to populate on drop down in my form. Could you do a tutorial for that. I tmay also help me better understand how you have multiple drop downs connecting a little better.
on August 25th, 2008 at 5:37 am
Hi,
I need a the coding to create a calculator in JAVA SCRIPT.
please send me
on September 5th, 2008 at 1:37 pm
Hey Jones,
If you still need the code to populate 1 drop down list let me know. (crafty@jeffcraft.ca)
Jeff
on September 12th, 2008 at 8:46 am
do you have a demo page with this..
on September 12th, 2008 at 12:25 pm
what is the purpose of this:
addOption(document.drop_list.SubCat, “”, “SubCat”, “”);
it doesn’t match the parameter taken by this:
function addOption(selectbox, value, text )
on September 12th, 2008 at 12:36 pm
huh?
a php with a language javascript.. this is interesting
on September 12th, 2008 at 12:37 pm
im talking about
language=”javascript” src=”update.php”
on September 12th, 2008 at 1:49 pm
@ geocine
I do not currently have a demo set up, but ill set one up for you.
language=”javascript” src=”update.php”
–This is calling a php file that has javascript in it. It simply means that the javascript that it is calling is going to create php that will need to be parsed.
on September 12th, 2008 at 1:54 pm
I mean that the php will generate the javascript. sorry – said it backwards. After the php is parsed (which happens on the server side and will be done first) there will be javascript remaining.
on September 12th, 2008 at 1:55 pm
this is just my opinion..
change this
if(document.drop_list.Category.value == ‘$nt4[category]‘)
to this
echo “if(document.drop_list.Category.value == \”{$nt4[category]}\”)
since it might turn out like this
if(document.drop_list.Category.value == ‘Geocine’s’)
that will generate javascript error..
on September 12th, 2008 at 2:19 pm
and this one to work out strings with apostrophe’s
echo “i = document.addRecord.events.value;”;
echo “if( !(i > \”{$nt4['category']}\” && i < \”{$nt4['category']}\”) ){”;
on September 12th, 2008 at 2:20 pm
oopps copied it straight from my edited code here it is..
echo “i = document.drop_list.Category.value;”;
echo “if( !(i > \”{$nt4['category']}\” && i < \”{$nt4['category']}\”) ){”;
on September 19th, 2008 at 5:24 am
this is a great job,you are quiet appreciated,please ive been finding it difficult to validate my work on HTML using javascript i will be so happy if you can just send me the most important steps to follow or codes in using javascript validate a form,also using javascript to create a function like solving some Mathematical problems, thanks Tayo
on September 24th, 2008 at 10:33 pm
Mike, thanks for taking the time to publish your code. Any update on a running example?
Cheers
on September 28th, 2008 at 2:22 am
I am new to php and mysql and this has been very helpful in my learning, however i have not been able to get it working. My page ends up with the 3 drop down menus, but it just says main, category and sub category. I am not getting the data from the server. I have copied your tutorial exactly, except one set of modifications which was my db connection string. I am not sure if this is an error on your site, but when i went to test the connection.php file i got an error. I had to put an ‘ after servername_dbname and database name to get it to work. The code below was just copied from your site and is missing the ‘.
$servername=’localhost’;
$dbusername=’servername_dbname;
$dbpassword=’password’;
$dbname=’database name;
When i go to the update.php page, i get function fillCategory(){ and all the data from the server, but i cant get the program to function.
Thanks,
Ben
on October 14th, 2008 at 10:11 am
This is a terrific idea for a script, but it doesn’t actually work. The first box will populate fine, but there is something wrong with the Javascript on the other two.
on October 14th, 2008 at 10:43 am
Regarding my earlier comment. The script works great. I had filled the first table with my own categories so they didn’t match with the rest of the script. My bad.
on October 23rd, 2008 at 9:26 am
Great script…
Just wondering, is it possible to insert the chosen option into SQL.
Eg, the person choose, male as grp option, which open up boots and pants.
In the person table, there is a id, grp_id and category _id…so it is possible to get the grp_id and category_id from javascript and post it into MYSQL.
Thanks!
on November 12th, 2008 at 9:33 pm
it can handle only max 60 data table
on January 7th, 2009 at 1:58 am
Hey, i find this code not working for me.
I have also tried to copy those functions inside my index page and renamed it to index.php, but i could see no options under drop down boxes.
I dont understand what went wrong.
— dskanth.99@gmail.com
on February 2nd, 2009 at 11:21 am
Do you have demo of this script , or can you please send me a link to download to whole script
advanced thanks
rakibul
on February 25th, 2009 at 7:58 am
I have an opposite case.
I have hard coded 2 drop down lists into a FORM
I want to retrieve the value of those drop down lists entered by user from database into the original form means when I populate my form from the databse it should show me the value selected on the drop down lists
any HELP”?
on May 20th, 2009 at 11:05 am
So where is the success.php?
on May 20th, 2009 at 12:26 pm
It should be located in the same folder as these files we created. I didn’t cover making that file as you can have id do whatever you like such as:
-Posting The Selection to a DB for that user
-Selecting The Subcategory selected from and and returning the results
-Many more…
It is really up to you whatever you want that file to do. This was just to show how to get the values for the drop-downs.
on May 22nd, 2009 at 2:08 am
We use the same concept. Downloading the page with drop down(which gets values from database) is very slow. How can I speed up the downloading? I need it in Urgent. Can anyone help me?
on June 13th, 2009 at 6:57 am
Dear Mike,
Your Script is excellent, it is just what I needed.
One problem that I seem to be encountering is getting lots of PHP Syntax errors, any ideas of how I could fix this?
Also it would be a great help if you could include the whole file.
Thanks
on June 13th, 2009 at 12:21 pm
What kind of syntax errors are you receiving. Maybe I can help you out a little?
on June 13th, 2009 at 2:09 pm
Well I am new to PHP and I am not completly sure how to fix some of the errors. I mainly recive Sytax Parse Errors, but I am worried about adding/deleting certain lines incase it ruins the form.
Have you recived any syntax errors when creating it?
Thanks again
on June 14th, 2009 at 4:34 am
Here is my code:
Update.php:
Sucess.php:
=0;i–)
{
selectbox.remove(i);
}
}
function addOption(selectbox, value, text )
{
var optn = document.createElement(”OPTION”);
optn.text = text;
optn.value = value;
selectbox.options.add(optn);
}
[
?>
Thanks Again, Hopefully you will be able to help me out!
on June 14th, 2009 at 4:38 am
Sorry about my previous post, it dosent seem to have posted all of the code. I will have to do it in sections.
on June 14th, 2009 at 4:39 am
<?php
require “connection.php”;
echo ”
function fillCategory(){
“;
$q1=mysql_query(”select * from `group`”);
echo mysql_error();
while($nt1=mysql_fetch_array($q1)){
echo “addOption(document.drop_list.Main, ‘$nt1[group]‘, ‘$nt1[group]‘);”;
}
function SelectCat(){
removeAllOptions(document.drop_list.Category);
addOption(document.drop_list.Category, “”, “Category”, “”);
$q2=mysql_query(”select distinct(`group`) from category”);
while($nt2=mysql_fetch_array($q2)){
echo “if(document.drop_list.Main.value == ‘$nt2[group]‘){”;
$q3=mysql_query(”select category from category where `group`=’$nt2[group]‘”);
while($nt3=mysql_fetch_array($q3)){
echo “addOption(document.drop_list.Category,’$nt3[category]‘, ‘$nt3[category]‘);”;
on June 14th, 2009 at 4:40 am
}
echo “}”;
}
function SelectSubCat(){
removeAllOptions(document.drop_list.SubCat);
addOption(document.drop_list.SubCat, “”, “SubCat”, “”);
$q4=mysql_query(”select distinct(`category`) from subcategory”);
while($nt4=mysql_fetch_array($q4)){
echo “if(document.drop_list.Category.value == ‘$nt4[category]‘){”;
$q5=mysql_query(”select subcategory from subcategory where `category`=’$nt4[category]‘”);
while($nt5=mysql_fetch_array($q5)){
echo “addOption(document.drop_list.SubCat,’$nt5[subcategory]‘, ‘$nt5[subcategory]‘);”;
}
echo “}”;
}
?>
on June 14th, 2009 at 4:41 am
Sucess.php:
=0;i–)
{
selectbox.remove(i);
}
}
function addOption(selectbox, value, text )
{
var optn = document.createElement(”OPTION”);
optn.text = text;
optn.value = value;
selectbox.options.add(optn);
}
[
?>
on June 14th, 2009 at 4:43 am
My previous post for sucess.php did not contain the start of the code so here it is:
=0;i–)
Sorry about the mess of the comments I have caused but hopefully you will be able to understand them!
Thanks
on June 14th, 2009 at 3:43 pm
What error codes are you getting? Do you have this deployed somewhere so I can see all the errors you are getting?
on June 15th, 2009 at 11:21 am
Dear Mike,
You can veiw both PHP Errors on my website at:
http://amateur-gardeners.co.uk/update.php
http://amateur-gardeners.co.uk/sucess.php
Has anyone else found that they have recived any errors?
Is there anything wrong with the code?
Thanks for your help
on June 15th, 2009 at 8:58 pm
Check the line before these errors for a missing semicolon ;
on June 17th, 2009 at 3:11 pm
Dear John,
This does not seem to work either. Any other suggestions?
Thanks
on June 18th, 2009 at 2:48 pm
Dear John,
Regarding my previous post, I have now managed to get them to work but I am having problems with the Javascript. It does not seem to populate the feilds.
Thanks Again
on October 3rd, 2009 at 5:28 am
Hi Mike,
A question that has been wracking my brains is how to get the working?
I need to show the dropdowns on the homepage and then the search page, so i put the variables in the url and take them to the search page and use them to show the dropdowns with the selected values.
Any ideas?
Cheers
Stuart
on October 3rd, 2009 at 5:29 am
sorry code got taken out. its the selected values in the option tag.
Cheers