Dynamic Dependent Select Box using jQuery and Ajax
We often see dynamic dependent select box in website, which are part of web form. Today, in this tutorial I will show how to use dynamic dependent select box using jQuery and AJAX.
I will use an example of country and city, when you will select any country, its cities will be available in the below select box without refresh or reload the page this is the power of AJAX.
Steps to Create Dynamic Dependent Select Box using jQuery and Ajax
- Create a Database
- Create Two Tables of County and City and Insert Data in Both Table
- Create a Database Connection File
- Create Index File Which Using JQuery and Ajax
- Create PHP Action File
1. Create a Database
Create a database with name parent_child_select. For this purpose run the following query in MySQL.
CREATE DATABASE parent_child_select;2. Create Two Tables of County and City and Insert Data in Both Table
Create two tables with name county and city and insert sample data in database by using the following queries. For your ease I have also attached the SQL file in download file just import this file in SQL and it will create tables with sample data.
CREATE TABLE IF NOT EXISTS `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(50) NOT NULL,
PRIMARY KEY (`country_id`)
);
CREATE TABLE IF NOT EXISTS `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` varchar(50) NOT NULL,
PRIMARY KEY (`city_id`)
);
INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES
(1, 'Karachi', '1'),
(2, 'Lahore', '1'),
(3, 'Jeddah', '2'),
(4, 'Riyadh', '2'),
(5, 'London', '3'),
(6, 'Liverpool', '3');
INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'Pakistan'),
(2, 'Saudi Arabia'),
(3, 'United Kingdom');
3. Create a Database Connection File
Create a file with name db.php and copy paste the below code in that file. Make sure that you changed your database, username, password and host.
<?php
// Enter your Host, username, password, database below.
// I left password empty because i do not set password on localhost.
$con = mysqli_connect("localhost","root","","parent_child_select");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>4. Create Index File Which Using JQuery and Ajax
Create a file with name index.php and copy paste the below code in this file. Note that I have added the jQuery and Ajax script in the footer of this file.
<html>
<head>
<title>Dynamic Dependent Select Box using jQuery and Ajax</title>
</head>
<body>
<div>
<label>Country :</label><select name="country" class="country">
<option value="0">Select Country</option>
<?php
include('db.php');
$sql = mysqli_query($con,"SELECT * FROM country");
while($row=mysqli_fetch_array($sql))
{
echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>';
} ?>
</select><br/><br/>
<label>City :</label><select name="city" class="city">
<option>Select City</option>
</select>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js">
</script>
<script type="text/javascript">
$(document).ready(function()
{
$(".country").change(function()
{
var country_id=$(this).val();
var post_id = 'id='+ country_id;
$.ajax
({
type: "POST",
url: "ajax.php",
data: post_id,
cache: false,
success: function(cities)
{
$(".city").html(cities);
}
});
});
});
</script>
</body>
</html>5. Create PHP Action File
Create a file with name ajax.php and paste the below script there.
<?php
include('db.php');
if($_POST['id']){
$id=$_POST['id'];
if($id==0){
echo "<option>Select City</option>";
}else{
$sql = mysqli_query($con,"SELECT * FROM `city` WHERE country_id='$id'");
while($row = mysqli_fetch_array($sql)){
echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>';
}
}
}
?>If you found this tutorial helpful so share it with your friends, developer groups and leave your comment.
Facebook Official Page: All PHP Tricks
Twitter Official Page: All PHP Tricks
If I use submit form in that src, how can I to do?
I didn’t understand your query? If you want to know how to submit form using Ajax and without page refresh, you can check out my other tutorial. https://www.allphptricks.com/submit-form-without-page-refresh-using-ajax-jquery-and-php/
Many thanks! Simple and straightforward.
Thanks for the appreciation.
Hi, just like to clarify, where did you get the “cities” inside function? I;m trying to make same program but with different data instead of country, it’s about activities
success: function(cities)
{
$(“.city”).html(cities);
Thank you
Sir thanks for the wonderful code. It has solved my problem.when I am inserting the data in the MySQL table it is showing the I’d number as we have echo I’d in value. In place of the id, I want to insert the country, state and city name in the table. How can I do it. Kindly please help me.
Then you will need to pass the name of country city in the value attribute.
Hi. When I try to store the variable from the Country selection using the $selected = $_POST[‘somename’]; I only get an country ID(int) passed into the variable. Do you have way of saving the selected country value to a variable without changing value=”‘.$row[‘country_id’].’
many thanks for this code. I have a question, can I use this code as a entry data to the database by get the ids (country_id and city_id) from combobox?
Thank you very much friend.
If It’s possible, Please:
I have another table (fname, lname, address), how to insert for ex.”Lahore” into my-table from this Dynamic Dependent Select Box?
thank you
Simply Run the Insertion command, where i have executed the dynamic dependent field query.
Many Many Thanks…
Dear Habib,
Thanks for the appreciation.
thank’s bro so useful work ..
Thanks for the appreciation 🙂
thanks dear, but i have to do this for one table instead of 3 .. in my table I have year, make, model and submodel columns .. now I have to make the first drop down to show year then second drop-down should be made till the end… how can I do this ?? thanks again
You will need to repeat the same process for all with different classes or IDs.
have you got the value for three dropdown i need the same
I’ve tried your code, but second drop down is not listing any value.
Download the code and try to run the same code first, also make sure that jQuery is loading on your PC, for this purpose your internet connection must be enabled.
Hello Sir
Great Work
In my case, the second dropdown is input type text
so how to show the related city in this input
I made some modification, but it doesn’t work
Thank you
rly helpfull!
tho I can’t figure out how to do this with a mssql database 🙁
Thank you for your great assistance.
Iam completely new to this. I have followed up your example and it worked. My problem is when I change the data type for country_id column to varchar(13) the second Dropdown menu doesnot display. What should I do?
Thank you
Country_id is the int that we use to match during drop down opening, you must need anything integer that will match the values.
If you do not want to keep it integer than add one more column and match them, then you can change country_id data type.
Thank you
Hi very useful tutorial, if we use three drop down boxes then what should i add to this tutorial
Hi Prakash, thanks that you like it, you can simple repeat the process again. If you are still not able to do it, you can hire me for this task. [email protected]
Hi
Question: 1
One question at the initial state Country box shows “Select a country” and City box shows “Select city”. After selecting and testing and when u select “Select a Country” for Country the City box is simply blank..can this be set as “Select City”?
Question: 2
How do I hide the City box on load meaning I want the city box to appear ONLY when a country is selected. When no Country is selected the city box should not be visible.
Appreciate your help on above query.
best rgds
kish
You can do anything where we are getting cities, you can add additional Select City Option in the top and to show hide, use jQuery to show and hide div based on country change function.
Hi Javed
Thanks for your reply. I tried using your code but dont seem to get the Jquery/Ajax piece that is at the footer of the index.php file to execute.
I have a main program that as shown below which calls your index.php file. I can get the primary menu and also display the secondary drop-down menu. However when I select any option in the primary drop down the secondary drop down menu does not change. looks like the Jquery/Ajax code is not being executed at all.
function showDiagpanel(str) {
var xhttp;
if (str == “No”) {
document.getElementById(“txtHint3”).innerHTML = “”;
return;
}
xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById(“txtHint3”).innerHTML = this.responseText;
}
};
xhttp.open(“GET”, “index.php?s=”+str, true);
xhttp.send();
}
Appreciate your help if u can give me a lead as to where I am going wrong with this.
Rgds
Kish
I will suggest that you should use ajax() method, you can easily handle via ajax() method.
var post_id = ‘id=’+ country_id;
Can you explain meaning of this two line?
Passing the value of selected country into post_id variable.
How to show value also, like if country UK, city London the value is 10.
To get the city value, simply use $(‘.city’).val(); it will get the value, for testing you can simply alert its value.
What if i need another select tag for street when user select city he will select street.
i am facing also same problem can u add antoher select tag..and chages in ajax and javascript
Dear Junaid,
You can add as many select boxes as you want, all you need is to create copy with different ID and variables to avoid conflict.
Respected Sir,
When I insert this value into another table then instead of name “id ” inserted. Please give me idea.
Dear Tariq, your query is also dependent on your sql table, first check either names are available in the table, second alert values in javascript to see the value of selected field. You are also not using html
What if i want to save index.php with .html extension (as index.html). What will the block of code i have to write if i want to call php block in another file and call from index.html?
I tried this code and its showing error as follow-
403 (Forbidden)
What to do?
I would suggest you first try to create a database connection, just make sure are you able to connect database or not. Then you can proceed further.
Yeah, i’ve already created database. And i’m successful with above code. But i want to save index.php as index.html. So i have to remove php code from that file and call it using ajax, like we did for 2nd select box. How to get options in first select box, using ajax?
And your above code is very useful 🙂
Hi Aniket, i am glad you found my tutorial helpful for you. If you want to add countries dynamically, you will need to do the same which i did for city select box, make sure that you have another parent for country as well so that based on its parents selection you can fetch related data.
Hello, Javed. Thank you very much for this great work. Please, help me with this: I need to add a custom link in each city, so that when I click on the city the url opens in the same window. What should I do?
Add link column in city table and as we are getting city from database, get a link with it and on click using javascript window function to open new window. For example: window.open(“https://www.yourwebsitelink.com”,”_self”)
Thanks Javed. I added the column “city_url” in city table, placing a url in each city. Then, I added
window.open(“.$row[city_url]”, “_self”);
in the ajax.php file but it didn’t work. What is wrong?
This is the whole code in ajax.php: http://jsfiddle.net/Hernan7dp/946hdab1
It will not work, because you are not getting URL in any html tag, you will need to get it in any html tag. And you can not spoil the select structure. All i can say that you can store url value in data-attribute in your select options. Currently i didn’t write any tutorial about it, but you can find its tutorial on the Internet. data-attribute are custom attribute which you can create your own.
Ok. Thank you very much, friend. I will search on the Internet. Have a good weekend.
Thank you very much friend. It is very useful my project. Thank you again.
You welcome 🙂
Hi there,
great tutorial! i’m having trouble implemented the last section. Once I choose an option from the first drop down box, the second drop down box goes completely blank, even removing the original default text. I have tried debugging this for many days and I have managed to find out that the problem lies in the AJAX file. For some reason it appears that I can’t connect to any sql server from the ajax file.
-I can connect to the ajax file fine as I can test
$id=$_POST[‘id’];
if($id==1){
echo “Selected item 1″;
and it works fine.
However, I can’t get any results to populate from my database. Do you know what I can do to continue debugging, or do you know what my problem could be? The sql query works fine in phpmyadmin, and the same query (with echo results) work fine in my index.php for the first option. As you can tell, this is very confusing!
$sql = mysqli_query($db,”SELECT * FROM item_table WHERE type_id=’$id'”);
while($row = mysqli_fetch_array($sql)){
echo ”.$row[‘item_name’].”;
}
any advice would be appriciated! Cheers!
I will suggest you that you just download my tutorial from the above download link and if you are using xampp then place the downloaded folder in it and make database connection changes and then test if it is working in your side or not. It should be working, make sure that you create a separate folder as i said, if you will try to use Ajax inside any other CMS may be it will not work, like WordPress have other requirements to work on Ajax, therefore first try my sample demo file on your host.
Hi, Thank you for a really quick reply.
Your demo seems to be working just fine. I tried using your demo as a starting point, changing your sql values to the ones in my database. Still no luck though! The query works just fine in index.php, but it seems I can’t even make a connection when accessing the database through ajax using my own credentials. I even tested the connection:
if($con){
echo ‘Connected’; };
else {
echo ‘Not Connected’>;
If the database connected, when I select my first dropdown bar (that works perfectly fine), the dropdown bar will read either not connected, or connected. Each time the bar will always say that there is no connection being made. I have no idea how this is an issue as your demo worked perfectly fine! Thanks for your suggestions though!
Well you can see that i made one db.php file and i call it on both pages, index.php and ajax.php so if your database connection is working fine on your index.php it should be working on your other page as well because this is same file. I think there may be some other issue, I prefer that you try to read my script again, may be you are facing issue because of some other reason.
Hi friend,
Can please give me a hint suppose you would like to display Country ID from the country Table in text Box. Demonstration with one single table
Nice bro!
hello Rehman, thank you for this article. I needed to change country for district and city for villages as it applies to my project and the script will not load the villages when i select a district. Please how can i get some help?
What if i need another select tag for street when user select city he will select street.
This is not easy as you can not find data easily all list of streets of all countries, however you will need to create another table for street with fields name something like this street_id, street_name and city_id.
And on ajax part also check if city is selected populate all street values in dependent field same as we did for city. Hope this help you.
to also load the streets when a city is selected, can i add this to the ajax page?
<?php
include('db.php');
if($_POST['id']){
$id=$_POST['id'];
if($id==0){
echo "Select City”;
}else{
$sql = mysqli_query($con,”SELECT * FROM `city` WHERE country_id=’$id'”);
while($row = mysqli_fetch_array($sql)){
echo ”.$row
[‘city_name’].”;
}
}
}
?>
<?php
include('db.php');
if($_POST['id']){
$id=$_POST['id'];
if($id==0){
echo "Select Street”;
}else{
$sql = mysqli_query($con,”SELECT * FROM `street` WHERE city_id=’$id'”);
while($row = mysqli_fetch_array($sql)){
echo ”.$row
[‘street_name’].”;
}
}
}
?>
Then what happen to the JQuery in the index page.
Can you please rewrite this script to include street option?
I would suggest you to use different field name in html select box and also in php script, if you use id for two select boxes then you will face duplicate issue, you can use city_id and check if city_id is set in PHP script then show street, you will also need to change it in Ajax script. Simply replace id with city_id on every call such as Ajax, Select box, and PHP script.
Firstly, thank you very much for this tutorial, It is a really good job.
Could you extend this solution to 3 steps?
Step 3 is only database connection, as we always need to create database connection if we want using data from database, you can search about it in details in Google if you do not have any idea about database and its connection.
Thank you very much friend.
I am really grateful for this article.
It is super useful to me.
I am glad that you find it helpful.