how to Display dynamic dropdown using table data

I need to show state depending on country selected from drop down. It uses the table data to fetch and find the related data using AJAX

Here is my HTML markup

<label for="country">Country</label>
                <select class="form-control" id="country">
                    <option value="">Select Country</option>
                    <?php 
                        $query = "SELECT * FROM countries";
                        $result = $con->query($query);
                        if ($result->num_rows > 0) {
                            while ($row = $result->fetch_assoc()) {
                                echo "<option value='{$row["country_refer"]}'>{$row['country_name']}</option>";
                            }
                        }
                        else
                        {
                            echo "<option value=''>Country not available</option>"; 
                        }
                    ?>
                </select><br>

                <!-- State drop down -->
                <label for="state">State</label>
                <select class="form-control" id="state">
                    <option value="">Select State</option>
                </select>
                <br>

This is my javascript code to post the option value to action.php page

<script type="text/javascript">
$(document).ready(function(){
    // Country dependent ajax
    $("#country").on("change",function(){
        var countryId = $(this).val();
        alert(countryId);
        if (countryId) {
            $.ajax({
                url :"action.php",
                type:"POST",
                cache:false,
                data:{countryId:countryId},
                success:function(data){

                    $("#state").html(data);

                    alert(data);

                }
            });
        }
    });
});

This is my action.php page

<?php 
// Include the database config file 
include_once 'dbConfig.php';

// Get country id through state name

$countryId = $_REQUEST['countryId'];

if (!empty($countryId)) {
    // Fetch state name base on country id
    $query = "SELECT * FROM states WHERE country_refer = {$countryId}";

    echo "$query";
    $result = $con->query($query);

    if ($result->num_rows > 0) 
    {
        while ($row = $result->fetch_assoc()) {

            echo '<option value="'.$row['id'].'">'.$row['state_name'].'</option>'; 

        }
    }
    else{
        echo '<option value="">State not available</option>'; 
    }
}

?>

The state doesn’t appear in my drop down and shows this error i have attached the screenshot here.

I use alert to find what happens here it throws this error.

This is my Sql file

   CREATE TABLE `countries` (
       `id` int(11) NOT NULL,
       `country_refer` varchar(50) NOT NULL,
       `country_name` varchar(50) NOT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      INSERT INTO `countries` (`id`, `country_refer`,   `country_name`VALUES
      (1, 'India', 'India'),
      (2, 'Pakistan' ,'Pakistan'),
      (3, 'America','America'),
      (4, 'China','China');



CREATE TABLE `states` (
     `id` int(11) NOT NULL,
     `country_refer` varchar(50) NOT NULL,
     `state_name` varchar(50) NOT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


      INSERT INTO `states` (`id`, `country_refer`, `state_name`) VALUES
          (1, 'India', 'Andhra Pradesh'),
          (2, 'India', 'Gujarat'),
          (3, 'America', 'Florida'),
          (4, 'America', 'New Jersey'),
          (5, 'China', 'Chongqing'),
          (6, 'China', 'Shanghai'),
          (7, 'India', 'Delhi'),
          (8, 'India', 'Mumbai');

Answer

Finally I found a solution for Displaying dynamic drop down with MYSQL table text data.

My AJAX library CDN (it needs to place in footer position)

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.2/jquery.min.js"></script>

My Database configuration code config.php

<?php

$con = mysqli_connect("localhost","root","","demos");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }  

?>

My HTML and PHP code to display Drop Down

 <!-- Drop down for state  -->

              <select onChange="getdistrict(this.value);"  name="state" id="state" class="form-control" >

                <option value="">Select</option>

                            <?php $query =mysqli_query($con,"SELECT * FROM state");
                      while($row=mysqli_fetch_array($query))
                      { ?>
                          <option value="<?php echo $row['Statenme'];?>"><?php echo $row['StateName'];?></option>
                      <?php
                      }
                      ?>
              </select>

                <!-- Drop down for district  -->

              <select name="district" id="district-list" class="form-control">
                <option value="">Select</option>
              </select>

My java script code to post the option value to filter the district using AJAX.here i post the data to get_district.PHP page.

<script>
                function getdistrict(val) {
                  $.ajax({
                  type: "POST",
                  url: "get_district.php",
                  data:'state_nme='+val,
                  success: function(data){
                    $("#district-list").html(data);
                  }
                  });
                }
              </script> 

My get_district.php code

<?php

require_once("config.php");

if(!empty($_POST["state_nme"])) 
{
    $query =mysqli_query($con,"SELECT * FROM district WHERE Statenme = '" . $_POST["state_nme"] . "'");
?>
<option value="">Select District</option>
<?php
    while($row=mysqli_fetch_array($query))  
{
?>
    <option value="<?php echo $row["DistrictName"]; ?>"><?php echo $row["DistrictName"]; ?></option>
<?php
}
}

?>

Here is my sql file

               CREATE TABLE `state` (
              `Statenme` varchar(100)  NOT NULL,
              `StateName` varchar(150) DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1;



            INSERT INTO `state` (`Statenme`, `StateName`) VALUES
            ('Andaman and Nicobar Island (UT)', 'Andaman and Nicobar Island (UT)'),
            ('Andhra Pradesh', 'Andhra Pradesh'),
            ('Arunachal Pradesh', 'Arunachal Pradesh'),
            ('Assam', 'Assam');


            CREATE TABLE `district` (
              `DistCode` int(11) NOT NULL,
              `Statenme` varchar(100) DEFAULT NULL,
              `DistrictName` varchar(200) DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1;



            INSERT INTO `district` (`DistCode`, `Statenme`, `DistrictName`) VALUES
            (1, 'Andaman and Nicobar Island (UT)', 'North and Middle Andama'),
            (2, 'Andaman and Nicobar Island (UT)', 'South Andama'),
            (3, 'Andaman and Nicobar Island (UT)', 'Nicobar'),
            (4, 'Andaman and Nicobar Island (UT)', 'Anantapur'),
            (5, 'Andaman and Nicobar Island (UT)', 'Chittoor'),
            (6, 'Andhra Pradesh', 'East Godavari'),
            (7, 'Andhra Pradesh', 'Guntur'),
            (8,'Andhra Pradesh', 'Krishna'),
            (9, 'Andhra Pradesh', 'Kurnool'),
            (10, 'Andhra Pradesh', 'Prakasam'),
            (11, 'Andhra Pradesh', 'Srikakulam'),
            (12, 'Andhra Pradesh', 'Sri Potti Sri Ramulu Nellore');

I actually found this solution here

Thanks a lot… 🙂