Sunday 6 March 2016

Dynamically generate a select list with jQuery, AJAX & PHP


In this tutorial we are going to learn dynamic dependent select box using jquery, ajax and php. This type of feature mostly use if you have use Country State City or you have working with Category and you want to load Sub Category of particular category. At that time this feature is very useful. But Here we have take an example of dynamic dependent drop down list box of Country, State and City. Here State data has been dependent on selection of Country. So when we have select particular country then in state select box we have load that country state only not all country state. Same as City data is also dependent on selection of state. So when we have select particular state then in city select box it will load only that state city only. So It will help us to filter records from large amount of records.

The dynamic dependent drop down box is basically used for making select box of Country State and City. So, In this post, we have developed dependent select box of country state city by using PHP Mysql as back end and Ajax JQuery as Front end. Here State data has been dependent on Country and city data has been dependent on state data. For make this feature we have use Ajax JQuery, So it load data without refreshing of page. This is our simple tutorial on Dynamic Dependent Select Box using JQuery Ajax with PHP


Online Demo


Dynamic Dependent Select Box using JQuery Ajax with PHP








Source Code


index.php



<?php
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$country = '';
$query = "SELECT country FROM country_state_city GROUP BY country ORDER BY country ASC";
$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($result))
{
 $country .= '<option value="'.$row["country"].'">'.$row["country"].'</option>';
}
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Webslesson Tutorial | Dynamic Dependent Select Box using JQuery Ajax with PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br /><br />
  <div class="container" style="width:600px;">
   <h2 align="center">Dynamic Dependent Select Box using JQuery Ajax with PHP</h2><br /><br />
   <select name="country" id="country" class="form-control action">
    <option value="">Select Country</option>
    <?php echo $country; ?>
   </select>
   <br />
   <select name="state" id="state" class="form-control action">
    <option value="">Select State</option>
   </select>
   <br />
   <select name="city" id="city" class="form-control">
    <option value="">Select City</option>
   </select>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){
 $('.action').change(function(){
  if($(this).val() != '')
  {
   var action = $(this).attr("id");
   var query = $(this).val();
   var result = '';
   if(action == "country")
   {
    result = 'state';
   }
   else
   {
    result = 'city';
   }
   $.ajax({
    url:"fetch.php",
    method:"POST",
    data:{action:action, query:query},
    success:function(data){
     $('#'+result).html(data);
    }
   })
  }
 });
});
</script>


fetch.php



<?php
//fetch.php
if(isset($_POST["action"]))
{
 $connect = mysqli_connect("localhost", "root", "", "testing");
 $output = '';
 if($_POST["action"] == "country")
 {
  $query = "SELECT state FROM country_state_city WHERE country = '".$_POST["query"]."' GROUP BY state";
  $result = mysqli_query($connect, $query);
  $output .= '<option value="">Select State</option>';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '<option value="'.$row["state"].'">'.$row["state"].'</option>';
  }
 }
 if($_POST["action"] == "state")
 {
  $query = "SELECT city FROM country_state_city WHERE state = '".$_POST["query"]."'";
  $result = mysqli_query($connect, $query);
  $output .= '<option value="">Select City</option>';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '<option value="'.$row["city"].'">'.$row["city"].'</option>';
  }
 }
 echo $output;
}
?>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `country_state_city`
--

CREATE TABLE IF NOT EXISTS `country_state_city` (
  `id` int(11) NOT NULL,
  `country` varchar(250) NOT NULL,
  `state` varchar(250) NOT NULL,
  `city` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `country_state_city`
--

INSERT INTO `country_state_city` (`id`, `country`, `state`, `city`) VALUES
(1, 'USA', 'New York', 'New York city'),
(2, 'USA', 'New York', 'Buffalo'),
(3, 'USA', 'New York', 'Albany'),
(4, 'USA', 'Alabama', 'Birmingham'),
(5, 'USA', 'Alabama', 'Montgomery'),
(6, 'USA', 'Alabama', 'Huntsville'),
(7, 'USA', 'California', 'Los Angeles'),
(8, 'USA', 'California', 'San Francisco'),
(9, 'USA', 'California', 'San Diego'),
(10, 'Canada', 'Ontario', 'Toronto'),
(11, 'Canada', 'Ontario', 'Ottawa'),
(12, 'Canada', 'British Columbia', 'Vancouver'),
(13, 'Canada', 'British Columbia', 'Victoria'),
(14, 'Australia', 'New South Wales', 'Sydney'),
(15, 'Australia', 'New South Wales', 'Newcastle'),
(16, 'Australia', 'Queensland', 'City of Brisbane'),
(17, 'Australia', 'Queensland', 'Gold Coast\r\n');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `country_state_city`
--
ALTER TABLE `country_state_city`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `country_state_city`
--
ALTER TABLE `country_state_city`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;

26 comments:

  1. Hi Thanks for the simple tutorial.

    Just have a quick one. How can i insert a country name and state name into the table? right now its showing the country and state IDs.

    Thanks in Advance.
    A

    ReplyDelete
  2. Thanks a lot. This saved my day!

    ReplyDelete
  3. this script dosen't work, if you have states with the same Name.

    ReplyDelete
  4. this script dosen't work, if you have states with the same name.

    ReplyDelete
  5. Hi thanks for this tutorial.

    If you don't mind. How can i add another drop down?

    Thanks in a lot.

    ReplyDelete
  6. Nice tutorial. It helps me a lot.
    Thanks.

    ReplyDelete
  7. Nice tutorial. it helps me a lot. it solve my problem.
    Thanks.

    ReplyDelete

  8. the demo does not work, there is a problem? thans

    ReplyDelete
  9. i have go through several tutorials since last 2 days but this is found best for me and best thing i have found about this that it has reduced no of tables in database and eliminating use of numerics for referencing...Welldone & Thanks

    I have a question here also i.e. i m using session storage to store values of these dependent boxes to show after page load and its fine for 1st dependent box (countryt box) but state and city select boxes are not getting values back from session storage ..... what will be solution for this.

    ReplyDelete
  10. Hello,
    how can I select the first two elements to get the third result. I need to make a query with where one_id = '' and two_id = ''

    ReplyDelete
  11. Thank you this code has helped me a lot in my final project.

    ReplyDelete
  12. Hey! it only fetch the country names not the states and cities! There is a problem.Thanks

    ReplyDelete
  13. Thank you for this tutorial. The problem I am now having is that when I save the data to the mysql table, it shows the country_id, not the country_name and like wise for state. I want to save the country and state name in the table. How do I get That?

    ReplyDelete
  14. How can i insert a country name and state name into the table? right now its showing the country and state IDs.

    ReplyDelete
  15. Very nice helpful tutorial for practice thank a lot. there is no error

    ReplyDelete