{"id":1539,"date":"2008-05-14T02:04:51","date_gmt":"2008-05-14T07:04:51","guid":{"rendered":"http:\/\/teamtutorials.com\/?p=1539"},"modified":"2008-05-14T02:04:51","modified_gmt":"2008-05-14T07:04:51","slug":"pulling-drop-down-values-from-a-database-using-javascript-and-php","status":"publish","type":"post","link":"https:\/\/teamtutorials.com\/web-development-tutorials\/pulling-drop-down-values-from-a-database-using-javascript-and-php","title":{"rendered":"Pulling Drop-down values from a database using Javascript and PHP"},"content":{"rendered":"
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\u2019s 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:<\/p>\n
\r\nCREATE TABLE `group` (\r\n `id` int(10) NOT NULL auto_increment,\r\n `group` varchar(100) NOT NULL default '',\r\n PRIMARY KEY (`id`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;\r\n<\/pre>\nThis will create a table called group and set it to have the fields below:<\/p>\n
Id \u2013 unique primary key, integer, auto-increments
\nGroup \u2013 name of the various groups (programming, networking, ect..)<\/p>\nIt 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.<\/p>\n
\r\nINSERT INTO `group` VALUES(1, 'Programming');\r\nINSERT INTO `group` VALUES(2, 'Graphic Design');\r\nINSERT INTO `group` VALUES(3, 'Networking');\r\nINSERT INTO `group` VALUES(4, 'Office Software');\r\nINSERT INTO `group` VALUES(5, 'Operating Systems');\r\nINSERT INTO `group` VALUES(6, 'Other');\r\nINSERT INTO `group` VALUES(7, '3D and Video');\r\nINSERT INTO `group` VALUES(8, 'Web Development');\r\nINSERT INTO `group` VALUES(9, 'Databases');\r\n<\/pre>\nThese queries will create the groups that we will be using to populate our first drop-down. Now we need to create our category table.<\/p>\n
\r\nCREATE TABLE `category` (\r\n `id` int(10) NOT NULL auto_increment,\r\n `group` varchar(100) NOT NULL default '',\r\n `category` varchar(100) NOT NULL default '',\r\n PRIMARY KEY (`id`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;\r\n<\/pre>\nThis will create a table called category with the following fields:<\/p>\n
Id \u2013 unique primary key, integer, auto-increments
\nGroup \u2013 name of the various groups from the previous drop-down (programming, networking, ect..)
\nCategory \u2013 name of the categories that will be listed underneath the corresponding groups<\/p>\nThis one also sets our auto-increment value for us so that when we insert the values next, we will start with the right number.<\/p>\n
\r\nINSERT INTO `category` VALUES(1, 'Graphic Design', 'Fireworks');\r\nINSERT INTO `category` VALUES(2, 'Graphic Design', 'Flash');\r\nINSERT INTO `category` VALUES(3, '3D and Video', '3d Studio Max');\r\nINSERT INTO `category` VALUES(4, '3D and Video', 'AutoCAD');\r\nINSERT INTO `category` VALUES(5, 'Web Development', 'HTML and XHTML');\r\nINSERT INTO `category` VALUES(6, 'Web Development', 'CSS');\r\nINSERT INTO `category` VALUES(7, 'Web Development', 'Other');\r\nINSERT INTO `category` VALUES(8, 'Programming', 'C and C++');\r\nINSERT INTO `category` VALUES(9, 'Programming', 'Java');\r\nINSERT INTO `category` VALUES(10, 'Databases', 'MySQL');\r\nINSERT INTO `category` VALUES(11, 'Databases', 'Oracle');\r\nINSERT INTO `category` VALUES(12, 'Operating Systems', 'Windows XP');\r\nINSERT INTO `category` VALUES(13, 'Operating Systems', 'Linux');\r\nINSERT INTO `category` VALUES(14, 'Operating Systems', 'Mac OS');\r\nINSERT INTO `category` VALUES(15, 'Business Apps', 'Excel');\r\nINSERT INTO `category` VALUES(16, 'Business Apps', 'Powerpoint');\r\nINSERT INTO `category` VALUES(17, 'Business Apps', 'Word');\r\nINSERT INTO `category` VALUES(18, 'Other', 'Tips and Tricks');\r\nINSERT INTO `category` VALUES(19, 'Other', 'Other');\r\nINSERT INTO `category` VALUES(20, 'Networking', 'TCP IP');\r\nINSERT INTO `category` VALUES(21, 'Networking', 'Cisco');\r\nINSERT INTO `category` VALUES(22, 'Office Software', 'Peachtree');\r\nINSERT INTO `category` VALUES(23, 'Office Software', 'QuickBooks');\r\n<\/pre>\nAs, 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.<\/p>\n
\r\nCREATE TABLE `subcategory` (\r\n `id` int(10) NOT NULL auto_increment,\r\n `category` varchar(100) NOT NULL default '',\r\n `subcategory` varchar(100) NOT NULL default '',\r\n PRIMARY KEY (`id`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 42;\r\n<\/pre>\nThis will create the following fields:<\/p>\n
Id \u2013 unique primary key, integer, auto-increments
\nCategory \u2013 name of the categories that will be listed underneath the corresponding groups that is related to those in the drop down.
\nSubcategory \u2013 The subcategories that belong to the corresponding category<\/p>\nNow we need to insert our data into this table: <\/p>\n
\r\nINSERT INTO `subcategory` VALUES(1, 'Fireworks', 'Text Effects');\r\nINSERT INTO `subcategory` VALUES(2, 'Fireworks', 'Other');\r\nINSERT INTO `subcategory` VALUES(3, 'Flash', 'Animation');\r\nINSERT INTO `subcategory` VALUES(4, 'Flash', 'Tweening');\r\nINSERT INTO `subcategory` VALUES(5, '3d Studio Max', 'Animation');\r\nINSERT INTO `subcategory` VALUES(6, '3d Studio Max', 'Effects');\r\nINSERT INTO `subcategory` VALUES(7, 'AutoCAD', '3D');\r\nINSERT INTO `subcategory` VALUES(8, 'AutoCAD', 'Architectural');\r\nINSERT INTO `subcategory` VALUES(9, 'AutoCAD', 'Basics');\r\nINSERT INTO `subcategory` VALUES(10, 'AutoCAD', 'Other');\r\nINSERT INTO `subcategory` VALUES(11, 'CSS', 'General');\r\nINSERT INTO `subcategory` VALUES(12, 'HTML and XHTML', '.htaccess');\r\nINSERT INTO `subcategory` VALUES(13, 'HTML and XHTML', 'Advanced');\r\nINSERT INTO `subcategory` VALUES(14, 'HTML and XHTML', 'Backgrounds');\r\nINSERT INTO `subcategory` VALUES(15, 'Java', 'Applet Building');\r\nINSERT INTO `subcategory` VALUES(16, 'Java', 'Application Building');\r\nINSERT INTO `subcategory` VALUES(17, 'C and C++', 'Development');\r\nINSERT INTO `subcategory` VALUES(18, 'C and C++', 'File Manipulation');\r\nINSERT INTO `subcategory` VALUES(19, 'MySQL', 'General');\r\nINSERT INTO `subcategory` VALUES(20, 'Oracle', 'General');\r\nINSERT INTO `subcategory` VALUES(21, 'Linux', 'Administration');\r\nINSERT INTO `subcategory` VALUES(22, 'Linux', 'Editing Files');\r\nINSERT INTO `subcategory` VALUES(23, 'Windows XP', 'Administration');\r\nINSERT INTO `subcategory` VALUES(24, 'Windows XP', 'Development');\r\nINSERT INTO `subcategory` VALUES(25, 'Mac OS', 'General');\r\nINSERT INTO `subcategory` VALUES(26, 'Mac OS', 'Networking');\r\nINSERT INTO `subcategory` VALUES(27, 'Excel', 'General');\r\nINSERT INTO `subcategory` VALUES(28, 'Powerpoint', 'General');\r\nINSERT INTO `subcategory` VALUES(29, 'Word', 'General');\r\nINSERT INTO `subcategory` VALUES(30, 'Networking', 'General');\r\nINSERT INTO `subcategory` VALUES(31, 'Tips and Tricks', 'Hacks');\r\nINSERT INTO `subcategory` VALUES(32, 'Tips and Tricks', 'OverClocking');\r\nINSERT INTO `subcategory` VALUES(33, 'Other', 'BASH');\r\nINSERT INTO `subcategory` VALUES(34, 'Other', 'MonitorTips');\r\nINSERT INTO `subcategory` VALUES(35, 'TCP IP', 'General');\r\nINSERT INTO `subcategory` VALUES(36, 'Cisco', 'Configuration');\r\nINSERT INTO `subcategory` VALUES(37, 'Cisco', 'Programming');\r\nINSERT INTO `subcategory` VALUES(38, 'Peachtree', 'Setup');\r\nINSERT INTO `subcategory` VALUES(39, 'Peachtree', 'Configuration');\r\nINSERT INTO `subcategory` VALUES(40, 'Quickbooks', 'Setup');\r\nINSERT INTO `subcategory` VALUES(41, 'Quickbooks', 'Configuration');\r\n<\/pre>\nNow 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<\/p>\n
\r\n<?php\r\n\r\n$servername='localhost';\r\n$dbusername='servername_dbname;\r\n$dbpassword='password';\r\n$dbname='database name;\r\n\r\nconnecttodb($servername,$dbname,$dbusername,$dbpassword);\r\nfunction connecttodb($servername,$dbname,$dbuser,$dbpassword)\r\n{\r\nglobal $link;\r\n$link=mysql_connect ("$servername","$dbuser","$dbpassword");\r\nif(!$link){die("Could not connect to MySQL");}\r\nmysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());\r\n}\r\n\r\n?>\r\n<\/pre>\nThis 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 \u2013 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.<\/p>\n
\r\n<html>\r\n<head>\r\n<title>Javascript Form Updater<\/title>\r\n<script language="javascript" src="update.php"><\/script>\r\n<\/head>\r\n\r\n<body bgcolor="#ffffff" onload="fillCategory();">\r\n\r\n<FORM name="drop_list" action="success.php" method="POST" >\r\n\r\n<SELECT NAME="Main" onChange="SelectCat();" >\r\n<Option value="">Main<\/option>\r\n<\/SELECT> \r\n<SELECT id="Category" NAME="Category" onChange="SelectSubCat();">\r\n<Option value="">Category<\/option>\r\n<\/SELECT>\r\n<SELECT id="SubCat" NAME="SubCat">\r\n<Option value="">SubCat<\/option>\r\n<\/SELECT>\r\n<\/form>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n<\/pre>\nThis 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<\/p>\n
\r\n<?php\r\n\r\nrequire "connection.php"; \r\necho "\r\n\r\nfunction fillCategory(){ \r\n\r\n";\r\n[\/sourcecode]\r\n\r\n 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.\r\n\r\n[sourcecode language='php']\r\n$q1=mysql_query("select * from `group`");\r\necho mysql_error();\r\nwhile($nt1=mysql_fetch_array($q1)){\r\necho "addOption(document.drop_list.Main, '$nt1[group]', '$nt1[group]');";\r\n}\r\n?>\r\n}\r\n<\/pre>\nNext, 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. <\/p>\n
\r\nfunction SelectCat(){\r\n\r\nremoveAllOptions(document.drop_list.Category);\r\naddOption(document.drop_list.Category, "", "Category", "");\r\n\r\n<?\r\n[\/sourcecode]\r\n\r\nNext, we create the next function. This function is run every time the group drop-down is changed. \r\n\r\n[sourcecode language='php']\r\n$q2=mysql_query("select distinct(`group`) from category");\r\n\r\nwhile($nt2=mysql_fetch_array($q2)){\r\n\r\necho "if(document.drop_list.Main.value == '$nt2[group]'){";\r\n$q3=mysql_query("select category from category where `group`='$nt2[group]'");\r\nwhile($nt3=mysql_fetch_array($q3)){\r\necho "addOption(document.drop_list.Category,'$nt3[category]', '$nt3[category]');";\r\n\r\n} \r\necho "}";\r\n}\r\n?>\r\n}\r\n<\/pre>\nThis 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.<\/p>\n
\r\nfunction SelectSubCat(){\r\n\r\nremoveAllOptions(document.drop_list.SubCat);\r\naddOption(document.drop_list.SubCat, "", "SubCat", "");\r\n\r\n<?\r\n\r\n$q4=mysql_query("select distinct(`category`) from subcategory");\r\n\r\nwhile($nt4=mysql_fetch_array($q4)){\r\n\r\necho "if(document.drop_list.Category.value == '$nt4[category]'){";\r\n$q5=mysql_query("select subcategory from subcategory where `category`='$nt4[category]'");\r\nwhile($nt5=mysql_fetch_array($q5)){\r\necho "addOption(document.drop_list.SubCat,'$nt5[subcategory]', '$nt5[subcategory]');";\r\n\r\n} \r\necho "}";\r\n}\r\n?>\r\n}\r\n<\/pre>\nThis 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. <\/p>\n
\r\nfunction removeAllOptions(selectbox)\r\n{\r\nvar i;\r\nfor(i=selectbox.options.length-1;i>=0;i--)\r\n{\r\nselectbox.remove(i);\r\n}\r\n}\r\n\r\n\r\nfunction addOption(selectbox, value, text )\r\n{\r\nvar optn = document.createElement("OPTION");\r\noptn.text = text;\r\noptn.value = value;\r\n\r\nselectbox.options.add(optn);\r\n}\r\n[\r\n<\/pre>\nFinally, 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.
\nNow, 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.
\n<\/p>\n","protected":false},"excerpt":{"rendered":"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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[24,17],"tags":[],"yoast_head":"\n
Pulling Drop-down values from a database using Javascript and PHP<\/title>\n\n\n\n\n\t\n\t\n\t\n