Dynamic selection how fetch 'Specific Cities of Specific State of Specific Country'?

Question

I have the following setup of a dynamic select options for Country, State and City using php and jquery ajax.

But the problem with this setup is, if two or more of the states have the same name, all of their associated cities become the output irrespective of country.

Like the image below (please imagine Canada has a state named California for the sake of this example):

How can I solve this problem, that is how can I get the output of Cities of State California of Country USA?

These are the sections I guess I need to improve. I have been trying a few methods but none of them is working. So I'll really appreciate any help.

The ajax:

$('.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);
         }
      })
   }
});

And the php query I have tried:

$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>';
    }

This is the full code in case you need to have a look: index.php

<?php
$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>';
}
?>

<select name="country" id="country" class="form-control action">
    <option value="">Select Country</option>
    <?php echo $country; ?>
</select>
<select name="state" id="state" class="form-control action">
    <option value="">Select State</option>
</select>
<select name="city" id="city" class="form-control">
    <option value="">Select City</option>
</select>


<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>

And the fetch.php

<?php

if (isset($_POST["action"])) {
    $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;
}
?>

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Nov 17 '17 at 15:47
[Have you watched the AJAX request / response in the browser's developer tools? Have you included the jQuery library in the project? Are there any errors reported? Are you running this on a web-server?](http://jayblanchard.net/basics_of_jquery_ajax.html) — Jay Blanchard, Nov 17 '17 at 15:51
Thanks @AlexHowansky for the suggestion. I'm learning to handle `php` and `mySql`. I'm on my way to learn the vulnerabilities. And may I have any suggestion regarding the solution of the above question? — Ashonko, Nov 17 '17 at 15:56
you need to distinguish how many selects are filled and build query with all selected parent values. (eg. SELECT a,b,c FROM tbl WHERE a=USA AND b=California — daremachine, Nov 17 '17 at 16:25
Thanks @daremachine. Can you please give an example? That would be really helpful. — Ashonko, Nov 17 '17 at 16:27

daremachine · Accepted Answer · 2017-11-17T16:56:31.647

You need populate next selects for all filled selects before and build right query for data.

@Example for populate CITY you need to know which are COUNTRY and STATE was selected.

PHP

if(isset($_POST['country']) && $_POST['country'] != ''
    && (!isset($_POST['state']) || $_POST['state'] == '') {
        // return STATES for selected COUNTRY
        $sql = "SELECT country, state FROM tbl WHERE country = {postCountry}";
}
else if(isset($_POST['country']) && $_POST['country'] != ''
    && isset($_POST['state']) && $_POST['state'] == '') {
        // return CITIES for selected COUNTRY and STATE
        $sql = "SELECT country, state, city FROM tbl WHERE country = {postCountry} AND state = {postState}";
}

This query

$query = "SELECT country FROM country_state_city GROUP BY country ORDER BY country ASC";

can be changed to DISTINCT

$query = "SELECT DISTINCT country FROM country_state_city ORDER BY country ASC";

JQUERY Is good approach to wrap data into form because it provides easy work with form elements like selects.

$('.action').change(function() {
    var formValues = $(this).closest('form').serialize();

    $.ajax({
      url: "fetch.php",
      method: "POST",
      data: formValues,
      success: function (data) {
          $('#' + result).html(data);
      }
  });
});

You can check DevTools Console on change and XHR request in Network in demo which values are sent in request to PHP.

DEMO JQUERY

Hope this help. Happy coding

Wow! Thanks for your effort. I'm on it to try but I guess I need to update the `ajax` section too because I'm sending `POST` as `action` not as `country` or `state`. Any suggestion? — Ashonko, Nov 17 '17 at 16:44
Thank you very much. I really appreciate your time but I'm getting syntax error in `fetch.php`. Can you please update the answer with the whole `fetch.php` if possible? — Ashonko, Nov 17 '17 at 17:10
You need change {postCountry} etc in $sql because postCountry is bad. I could write WHERE country = $_POST['country], but this is vulnerable to sql injects as @Alex said. I provide only idea how to solve your problem but not whole solution. You are programmer. you ask, I only want help to give a kick how to solve. But you need to implement yourself ours ideas. — daremachine, Nov 17 '17 at 17:44

Dynamic selection how fetch 'Specific Cities of Specific State of Specific Country'?

1 Answers1