使用下拉串列從mysql資料庫中修復資料搜索代碼-有解無憂

我正在嘗試編輯本教程中的代碼：[https://www.youtube.com/watch?v=fSIg5y5QXbc&ab_channel=Webslesson][1]。問題是當兩個或多個國家具有相同的州名時（僅限想象）。例如，美國和俄羅斯有相同的“xyz”狀態，當我選擇美國和狀態：“xyz”時，程式看不出有什么區別（好像它只是忘記了我們在哪個國家）并顯示所有城市兩國的“xyz”狀態。你能幫我解決這個問題嗎？

編輯：我想我已經接近了，但我需要知道如何保存例如： $select_country=(selected country) 在 fetch.php 檔案中

索引.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 class="container" 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" 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" multiple class="form-control">
    </select>
    <br />
    <input type="hidden" name="hidden_city" id="hidden_city" />
    <input type="submit" name="insert" id="action" class="btn btn-info" 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>

獲取.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;
}

?>

我不添加“insert.php”，因為我什至不使用它

uj5u.com熱心網友回復：

對于這種情況，國家、州和城市都應該存盤在單獨的表中。然后你在外鍵的幫助下鏈接它們。

但是由于在您的情況下所有記錄都位于一個表中，因此您可以將所選國家/地區添加到您的查詢中，以確保只有屬于特定國家和/或州的記錄受到影響。

如下所示，var country = $('#country').val();獲取所選國家并添加到 ajax 請求中。然后，包含在select服務器上的查詢中。

索引.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 class="container" 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" 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" multiple class="form-control">
            </select>
            <br />
            <input type="hidden" name="hidden_city" id="hidden_city" />
            <input type="submit" name="insert" id="action" class="btn btn-info" 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>

獲取.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;
}

?>

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/424079.html

標籤：php jQuery mysql sql 阿贾克斯

上一篇：Ajax請求-獲取表單資料并回圈遍歷element_name[]

下一篇：如何在ajaxjquery中通過資料序列化獲取checkox的多值？