WHAT THIS BLOG ACTUALLY INCLUDES
In this blog I am creating three dynamic and dependent select boxes.These select boxes option list are dependent on each other.
If the user selects any option from 1st list then the second list gets populated.Likewise, when the user selects the option from the second box then third select box options gets populated from the database.
SELECT BOXES DESCRIPTION
- First Select Box – It will contain the list of company(For Example – Samsung).
- Second Select Box – It will contain the list of items manufactured by the companies listed in the first box(For Example – Samsung’s Mobile,Washing Machine,Air Purifier etc).
- Third Select Box – It will contain the list of model names that comes for each item listed in second box(For Example – If user selects Mobile in second list then this list will show all the models of the mobile phone like Samsung Galaxy Note10).
These boxes will show the dynamic options list which I am fetching from database.Hence I created three tables in the database
DATABASE STRUCTURE
1.Table with the name – Company :- This table will have two columns – id,company_name.Below are the queries for creating and inserting data in the table.
1 2 3 4 |
CREATE TABLE company ( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, company_name varchar(100) NOT NULL ); |
1 2 3 4 5 |
Insert into company (company_name) values ("Samsung"), ("Apple"), ("Philips"); |
2. Table with the name – Item :- This table will have three columns – id,item_name,company_id.To make item’s select box dependent on company’s select box, company_id column will work as a foreign key and will be same as the id in the Company table.
1 2 3 4 5 |
CREATE TABLE item ( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, item_name varchar(100) NOT NULL, company_id varchar(100) NOT NULL ); |
1 2 3 4 5 6 7 8 9 |
insert into item (item_name, company_id) VALUES ("Samsung Mobile", "1"), ("Samsung AirPurifier", "1"), ("Samsung TV", "1"), ("Apple iPad", "2"), ("Apple iPhone", "2"), ("Philips Dryer", "3"), ("Philips HeadPhone", "3"); |
3. Table with the name – Model :- This table will also contain three columns – id,model_name,item_id.To make model’s select box dependent on item’s select box, item_id column will work as a foreign key and will be same as the id in the Item table.
1 2 3 4 5 |
CREATE TABLE model ( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, model_name varchar(100) NOT NULL, item_id varchar(100) NOT NULL ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
insert into model (model_name, item_id) VALUES ("Galaxy Note10", "1"), ("Galaxy Z Flip", "1"), ("AX3000", "2"), ("AX5500", "2"), ("T4500 Smart TV", "3"), ("TUE60F Smart Crystal TV", "3"), ("iPad Pro", "4"), ("iPad Air", "4"), ("iPad Mini", "4"), ("iPhone 11 Pro", "5"), ("iPhone SE", "5"), ("iPhone Xr", "5"), ("ThermoProtect Hairdyer", "6"), ("DryCare Advanced", "6"), ("Hi-Res Audio Wireless", "7"), ("UpBeat In-ear headphones", "7"), ("Bluetooth Headset", "7"); |
FILE STRUCTURE
Three files are used for implementing dynamic select box logic.
- config.php – for creating database connection.
- select.php – for creating three select boxes and making ajax request for populating the options in the box.
- ajax.php – for responding to all the request received from select.php by querying the database and fetching the table data and sending it back to select.php
CONFIG.PHP
1 2 3 4 5 6 7 8 9 |
<?php $host="localhost"; $username="root"; $password=""; $dbname="mytestdb"; $con=mysqli_connect($host,$username,$password,$dbname); ?> |
SELECT.PHP
- In the Head Section of this file I am using CDN for JQUERY and BootStrap(for designing purpose).
- In the Body Section I am using bootstrap classes(container,jumbotron,form-group,custom-select) for a better UI with three select menus.
- First select menu contains onchange event which will trigger and call getItemList() function when user will select any option from 1st menu. getItemList() function will populate the 2nd list.Likewise, when user selects any option from 2nd menu, onchange event triggers and calls getModellist() function which populates the 3rd select box list.
- In the Script Section – first part is used to populate the first select box list as soon as the page loads and this is done using AJAX request.
- AJAX request calls ajax.php file for querying the database and fetching the companies name from Company Table.
- In ajax.php, after querying the database the query result will be in array format as it will contain multiple company id and company name. But ajax request does not accept array as response so we need to convert array into JSON format before sending it back to select.php.
IMPORTANT – If you are sending array as a response to AJAX, it will process the response as a string and not as an array so it better to send JSON as a response.
- AJAX request contains a parameter called dataType and it should be set to JSON to accept the JSON response from ajax.php file.For more details on AJAX parameters refer to https://api.jquery.com/jQuery.ajax/
- AJAX request parse the JSON response and convert it into an array. Hence forEach() function is used to access each array element and create options to populate the list in company select box.
- Function getItemList() and getModelList() are also using the AJAX request in the same way which is discussed above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
<!DOCTYPE html> <html> <head> <title>Dynamic Select Menu</title> <!-- BootStrap CSS CDN--> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css"> <!-- jQuery CDN --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <!-- JavaScript CDN For BootStrap --> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script> </head> <body> <div class="container"> <div class="jumbotron"> <div class="form-group"> <select onchange="getItemList(this.value)" id="company_list" class="custom-select"> <option>Select Company</option> </select> </div> <div class="form-group"> <select onchange="getModelList(this.value)" id="item_list" class="custom-select"> <option>Select Item</option> </select> </div> <div class="form-group"> <select id="model_list" class="custom-select"> <option>Select Model</option> </select> </div> </div> </div> </body> <script type="text/javascript"> $(document).ready(function(){ let tag ="companyList"; let select_menu=$('#company_list')[0]; // this expression is same as document.getElementById('dynamic_menu') $.ajax({ url:"ajax.php", dataType:"json", method:"post", data:{tag:tag}, success:function(response){ //alert(response.length); console.log($.isArray(response)); // if response is an array, this function will return true response.forEach((item,index)=>{ console.log(index,item); var option = document.createElement("option"); option.value = item['id']; option.text = item['company_name']; select_menu.appendChild(option); }) } }) }); //Getting Item List on the basis of company_id function getItemList(company_id) { let tag = "itemList"; let itemMenu =$('#item_list')[0]; //Removing all the old options from item list and model list and adding only one option in one go $('#item_list').empty().append('<option>Select Item</option>'); $('#model_list').empty().append('<option>Select Model</option>'); $.ajax({ url:"ajax.php", dataType:"json", method:"post", data:{tag:tag,company_id:company_id}, success:function(response){ response.forEach((item,index)=>{ console.log(index,item); var option = document.createElement("option"); option.value = item['id']; option.text = item['item_name']; itemMenu.appendChild(option); }) } }) } //Getting model list on the basis of item id function getModelList(item_id) { let tag = "modelList"; let modelMenu =$('#model_list')[0]; //Removing all the select options and adding only one option in one go $('#model_list').empty().append('<option>Select Model</option>'); $.ajax({ url:"ajax.php", dataType:"json", method:"post", data:{tag:tag,item_id:item_id}, success:function(response){ response.forEach((item,index)=>{ //console.log(index,item); var option = document.createElement("option"); option.value = item['id']; option.text = item['model_name']; modelMenu.appendChild(option); }) } }) } </script> </html> |
AJAX.PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
<?php include_once 'config.php'; if($_POST['tag']=='companyList') { $query = "select * from company"; $result = mysqli_query($con,$query); $arr =[]; $i=0; while($row = mysqli_fetch_assoc($result)) { $arr[$i] = $row; $i++; } echo json_encode($arr); } // Getting item list on the basis of company_id if($_POST['tag']=='itemList') { $company_id = $_POST['company_id']; $query = "select * from item where company_id ='".$company_id."'" ; $result = mysqli_query($con,$query); $arr =[]; $i=0; while($row = mysqli_fetch_assoc($result)) { $arr[$i] = $row; $i++; } echo json_encode($arr); } // Getting model list on the basis of item_id if($_POST['tag']=='modelList') { $item_id = $_POST['item_id']; $query = "select * from model where item_id ='".$item_id."'" ; $result = mysqli_query($con,$query); $arr =[]; $i=0; while($row = mysqli_fetch_assoc($result)) { $arr[$i] = $row; $i++; } echo json_encode($arr); } ?> |
please help me
I want show details subcategory 3nd from databse
example I selected APPLE —>>>APPLE IPAD—>>>IPAD PRO
and result show exaple memory card-camera-megapixel ,…… for IPAD PRO
HOW?
I’m using this snippet as part of a larger . I’m having trouble figuring out what the $_POST variable is for the item_id (I’ve tried $_POST[‘model_list’] and $_POST[‘modelList’] and neither returned a value.
Hello can you convert it to PDO. Thanks in advance
I do the same but all select box are empty .it show only Select company.Select item .SM .can u help please
Hi, I am trying to use this, but the first dropdown just populates with many undefined. I can’t see what the issue is?
Hi, I tried to send items from dropdown box via email but it sends only the id instead of sending the name of the item.
How can I fix this?
very nice job but i have one question how can we make it in case there is no second or third level ( item, model) to avoid showing the dropdown for it. i mean did not show the secound or third drowp dwon select
thx in advance
Hi Martin, Just remove the itemlist and modellist part of code from select.php and ajax.php files and then you will get only one select menu with dynamic items.