Dependant Drop Down List With Search Box

Question

I have three tables:

Countries [CountryId, CountryName]
States [StateId, StateName, CountryId_FK]
Cities [CityId, CityName, CityInfo, StateId_FK]

On my html form, I am able to list Countries & States in two dependant drop downs using PHP & Ajax. The States drop down is dependant on the country drop down which is working fine.

However, I am stuck trying to have the input text box search for a city based on the selection of the state and if the city exists, it should display the CityName and CityInfo preferably to a table.

Below is the php code where I think I am stuck at.

    if (isset($_POST['StateId'])){
        $query = "SELECT * FROM tblCities WHERE StateId=".$_POST['StateId'] ;
        // GET Search here???
        $result = $conn->query($query);
        if ($result->num_rows > 0){
        //DO SOMETHING HERE
        }

NOTE: I can echo results into a table just fine but I want to SEARCH for a city first before displaying the match.

ajaxdata.php

if (isset($_POST['CountryId'])){
    $query = "SELECT * FROM tblStates WHERE CountryId=".$_POST['CountryId'];
    $result = $conn->query($query);
    if ($result->num_rows > 0){
        echo '<option selected disabled value="">Select States</option>';
        while ($row = $result->fetch_assoc()){
            echo '<option value='.$row['StateId'].'>'.$row['StateName'].'</option>';
            json_encode($row, $name);
        }
    }else {
        echo '<option>No State Found!</option>';

    }
}

functions.js

function FetchCountry(id){
    $('#countries').html(''); 
    $('#states').html(''<option selected disabled>Select State</option>');
    $.ajax ({
        type:'post',
        url:'ajaxdata.php',
        data : {CountryId : id},
        success : function(data,){
            $('#states').html(data);
            console.log(data); //Log data
        }
    });
}
function FetchStates(id){
    $('#cities').html('');
    $.ajax ({
        type:'post',
        url:'ajaxdata.php',
        data : {StateId : id},
        success : function(data,){
            $('#cities').html(data);
            console.log(data);
        }
    });
}

index.php

<?php
include "database.php";
$query = "SELECT * FROM tblCountries";
?>

<!DOCTYPE html>
//CSS/JS/JQUERY here
 <head>
  <script src="functions.js">
 </head>
 <body>
  <div class="container">
   <form method="POST">
     <select id="countries" onchange="FetchCountries(this.value)">
      <option selected disabled>Select Country</option>
        <php?
             if ($result->num_rows > 0){
             while ($row = $result->fetch_assoc()){
             echo '<option value='.$row['CountryId'].'>'.$row['CountryName'].'</option>';
            }
          }
         ?>
     <select id="states" onchange="FetchStates(this.value)">
      <option selected disabled>Select State</option>
     <input type="text" id="search" class="form-control"></input>
     <button class="btn-Search" name="submit" type="submit"></button>
  </form>
  </div>
  <--Results Area -->
   <div class="container">
    <table id="cities"></table>
   </div>
 </body>
</html>

Also show the PHP AJAX code that you have tried. It will be good if you show some form or box what exactly you want to show on what basis. — John Doe

Bernhard Beatus Bernhard Beatus · Accepted Answer · 2021-05-31T06:30:59

You should change your sql statements to this:

$query="SELECT Cities.CityId, Cities.CityName, Cities.CityInfo
FROM Cities
JOIN States ON Cities.StatedId_FK=States.StatedId
WHERE States.StatedId = '".$_POST['StateId']."'";

$query="SELECT Countries.CountryId, Countries.CountryName
FROM Countries
JOIN States ON Countries.CountryId=States.CountryId_FK
WHERE CountryId='".$_POST['CountryId']."'";

See this tutorial: https://www.php-einfach.de/mysql-tutorial/mysql-left-join/

you can do something like this:

<input type="text" id="search" onkeyup="showCities(this.value) class="form-control"></input>

And then make a function showCities like function FetchStates.

Dependant Drop Down List With Search Box

1 Answers