repairing data search code from mysql database using dropdown list-CodePudding

I'm trying edit a code from this tutorial: [https://www.youtube.com/watch?v=fSIg5y5QXbc&ab_channel=Webslesson][1]. The problem is a sytuation when two or more countires have the same state name (imagination only). For example, USA and Russia have the same "xyz" state, and when I select USA and state: "xyz", the program sees no difference (as if it just forgot which country we are in) and displays all the cities in the "xyz" state from both country. Can you help me resolve this problem?

EDIT: I think i'm close, but i need to know, how to save for example: $select_country=(selected country) in fetch.php file

index.php

<?php
//index.php

include('database_connection.php');

$country = '';

$query = "
 SELECT country FROM country_state_city GROUP BY country ORDER BY country ASC
";
$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

foreach($result as $row)
{
 $country .= '<option value="'.$row["country"].'">'.$row["country"].'</option>';
}

?>
<!DOCTYPE html>
<html>
 <head>
  <title>Insert Dynamic Multi Select Box Data using Jquery Ajax 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>
  <script src="jquery.lwMultiSelect.js"></script>
  <link rel="stylesheet" href="jquery.lwMultiSelect.css" />
 </head>
 <body>
  <br /><br />
  <div  style="width:600px;">
   <h2 align="center">Insert Dynamic Multi Select Box Data using Jquery Ajax PHP</h2><br /><br />
   <form method="post" id="insert_data">
    <select name="country" id="country" >
     <option value="">Select Country</option>
     <?php echo $country; ?>
    </select>
    <br />
    <select name="state" id="state" >
     <option value="">Select State</option>
    </select>
    <br />
    <select name="city" id="city" multiple >
    </select>
    <br />
    <input type="hidden" name="hidden_city" id="hidden_city" />
    <input type="submit" name="insert" id="action"  value="Insert" />
   </form>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){

 $('#city').lwMultiSelect();

 $('.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);
     if(result == 'city')
     {
      $('#city').data('plugin_lwMultiSelect').updateList();
     }
    }
   })
  }
 });

 $('#insert_data').on('submit', function(event){
  event.preventDefault();
  if($('#country').val() == '')
  {
   alert("Please Select Country");
   return false;
  }
  else if($('#state').val() == '')
  {
   alert("Please Select State");
   return false;
  }
  else if($('#city').val() == '')
  {
   alert("Please Select City");
   return false;
  }
  else
  {
   $('#hidden_city').val($('#city').val());
   $('#action').attr('disabled', 'disabled');
   var form_data = $(this).serialize();
   $.ajax({
    url:"insert.php",
    method:"POST",
    data:form_data,
    success:function(data)
    {
     $('#action').attr("disabled", "disabled");
     if(data == 'done')
     {
      $('#city').html('');
      $('#city').data('plugin_lwMultiSelect').updateList();
      $('#city').data('plugin_lwMultiSelect').removeAll();
      $('#insert_data')[0].reset();
      alert('Data Inserted');
     }
    }
   });
  }
 });

});
</script>

fetch.php

<?php
//fetch.php

if(isset($_POST['action']))
{
 include('database_connection.php');

 $output = '';

 if($_POST["action"] == 'country')
 {
  $query = "
  SELECT state FROM country_state_city 
  WHERE country = :country 
  GROUP BY state
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':country'  => $_POST["query"]
   )
  );
  $result = $statement->fetchAll();
  $output .= '<option value="">Select State</option>';
  foreach($result as $row)
  {
   $output .= '<option value="'.$row["state"].'">'.$row["state"].'</option>';
  }
 }
 if($_POST["action"] == 'state')
 {
  $query = "
  SELECT city FROM country_state_city 
  WHERE state = :state
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':state'  => $_POST["query"]
   )
  );
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output .= '<option value="'.$row["city"].'">'.$row["city"].'</option>';
  }


 }
 echo $output;
}

?>

I don't add "insert.php" because I don't even use it

CodePudding user response：

For situations like this, the countries, states and cities should all be store in separate tables. Then you link them with the help of foreign keys.

But since all the records live in one table in your case, you can add the selected country to your queries to ensure that only records belonging to a particular country and/or state are affected.

As you can see below, var country = $('#country').val(); gets the selected country and was added to the ajax request. And then, included in the select query on the server.

index.php

<?php
//index.php

include('database_connection.php');

$country = '';

$statement = $connect->prepare("
    SELECT country 
    FROM country_state_city 
    GROUP BY country 
    ORDER BY country ASC
");

$statement->execute();

while($row=$statement->fetch()){
    $country .= '<option value="'.$row["country"].'">'.$row["country"].'</option>';
}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Insert Dynamic Multi Select Box Data using Jquery Ajax 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>
    <script src="jquery.lwMultiSelect.js"></script>
    <link rel="stylesheet" href="jquery.lwMultiSelect.css" />
</head>
<body>
    <br /><br />
    <div  style="width:600px;">
        <h2 align="center">Insert Dynamic Multi Select Box Data using Jquery Ajax PHP</h2><br /><br />
        <form method="post" id="insert_data">
            <select name="country" id="country" >
                <option value="">Select Country</option>
                <?php echo $country; ?>
            </select>
            <br />
            <select name="state" id="state" >
                <option value="">Select State</option>
            </select>
            <br />
            <select name="city" id="city" multiple >
            </select>
            <br />
            <input type="hidden" name="hidden_city" id="hidden_city" />
            <input type="submit" name="insert" id="action"  value="Insert" />
        </form>
    </div>

    <script>
        $(document).ready(function(){
            $('#city').lwMultiSelect();

            $('.action').change(function(){
                if( $(this).val() != '' ){
                    var action  = $(this).attr("id");
                    var query   = $(this).val();
                    var result  = action == 'country' ? 'state' : 'city';
                    var country = $('#country').val();

                    $.ajax({
                        url:'fetch.php',
                        method:"POST",
                        data:{action:action, query:query, country: country},
                        success:function(data){
                            $('#' result).html(data);
                            if(result == 'city'){
                                $('#city').data('plugin_lwMultiSelect').updateList();
                            }
                        }
                    })
                }
            });
        });
    </script>
</body>
</html>

fetch.php

<?php
//fetch.php

if(isset($_POST['action'])){
    include('database_connection.php');

    $output = '';

    if($_POST["action"] == 'country'){
        $statement = $connect->prepare("
            SELECT state FROM country_state_city 
            WHERE country = :country 
            GROUP BY state
        ");
        $statement->execute([
            ':country' => $_POST["query"]
        ]);

        $output .= '<option value="">Select State</option>';
        while($row=$statement->fetch()){
            $output .= '<option value="'.$row["state"].'">'.$row["state"].'</option>';
        }
    }else if($_POST["action"] == 'state'){
        $statement = $connect->prepare("
            SELECT city FROM country_state_city 
            WHERE state = :state 
            AND country=:country
        ");
        $statement->execute([
            ':state'   => $_POST["query"],
            ':country' => $_POST["country"]
        ]);

        while($row=$statement->fetch()){
            $output .= '<option value="'.$row["city"].'">'.$row["city"].'</option>';
        }
    }

    echo $output;
}

?>