PHP 2选择选项始终与数据库中的表行同步

Question

I have 2 SelectOption where if Select 1 is selected, then Select 2 automatically synchronizes with Select 1

all SelectOptions contents retrieve from a database table.

SELECT 1 & 2 scripts:

<select  name="leavetype" autocomplete="off">
<option value="">Choose Leave Type</option>
<?php $sql = "SELECT LeaveType from tblleavetype";
$query = $dbh -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
$cnt=1;
if($query->rowCount() > 0)
{
foreach($results as $result)
{   ?>                                            
<option value="<?php echo htmlentities($result->LeaveType);?>"><?php echo htmlentities($result->LeaveType);?></option>
<?php }} ?>
</select>


<select  name="rightsgranted" autocomplete="off">
<option value="">Rights Granted</option>
<?php $sql = "SELECT RightsGranted from tblleavetype";
$query = $dbh -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
$cnt=1;
if($query->rowCount() > 0)
{
foreach($results as $result)
{   ?>                                            
<option value="<?php echo htmlentities($result->RightsGranted);?>"><?php echo htmlentities($result->RightsGranted);?></option>
<?php }} ?>
</select>

example

</div>

Answer 1

you need to use ajax for this work

this example is for the select city of a state

index.php

    <?php
//Include the database configuration file
include 'dbConfig.php';

//Fetch all the country data
$query = $db->query("SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC");

//Count total number of rows
$rowCount = $query->num_rows;
?>
<select id="country">
    <option value="">Select Country</option>
    <?php
    if($rowCount > 0){
        while($row = $query->fetch_assoc()){ 
            echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>';
        }
    }else{
        echo '<option value="">Country not available</option>';
    }
    ?>
</select>
<select id="state">
    <option value="">Select country first</option>
</select>
<select id="city">
    <option value="">Select state first</option>
</select>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $('#country').on('change',function(){
            var countryID = $(this).val();
            if(countryID){
                $.ajax({
                    type:'POST',
                    url:'ajaxData.php',
                    data:'country_id='+countryID,
                    success:function(html){
                        $('#state').html(html);
                        $('#city').html('<option value="">Select state first</option>'); 
                    }
                }); 
            }else{
                $('#state').html('<option value="">Select country first</option>');
                $('#city').html('<option value="">Select state first</option>'); 
            }
        });
        
        $('#state').on('change',function(){
            var stateID = $(this).val();
            if(stateID){
                $.ajax({
                    type:'POST',
                    url:'ajaxData.php',
                    data:'state_id='+stateID,
                    success:function(html){
                        $('#city').html(html);
                    }
                }); 
            }else{
                $('#city').html('<option value="">Select state first</option>'); 
            }
        });
    });
    </script>

<select id="state">
        <option value="">Select country first</option>
    </select>
    <select id="city">
        <option value="">Select state first</option>
    </select>

ajax.php

<script type="text/javascript">
    $(document).ready(function(){
        $('#country').on('change',function(){
            var countryID = $(this).val();
            if(countryID){
                $.ajax({
                    type:'POST',
                    url:'ajaxData.php',
                    data:'country_id='+countryID,
                    success:function(html){
                        $('#state').html(html);
                        $('#city').html('<option value="">Select state first</option>'); 
                    }
                }); 
            }else{
                $('#state').html('<option value="">Select country first</option>');
                $('#city').html('<option value="">Select state first</option>'); 
            }
        });
        
        $('#state').on('change',function(){
            var stateID = $(this).val();
            if(stateID){
                $.ajax({
                    type:'POST',
                    url:'ajaxData.php',
                    data:'state_id='+stateID,
                    success:function(html){
                        $('#city').html(html);
                    }
                }); 
            }else{
                $('#city').html('<option value="">Select state first</option>'); 
            }
        });
    });
    </script>

ajaxData.php

<?php
//Include the database configuration file
include 'dbConfig.php';

if(!empty($_POST["country_id"])){
    //Fetch all state data
    $query = $db->query("SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC");
    
    //Count total number of rows
    $rowCount = $query->num_rows;
    
    //State option list
    if($rowCount > 0){
        echo '<option value="">Select state</option>';
        while($row = $query->fetch_assoc()){ 
            echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>';
        }
    }else{
        echo '<option value="">State not available</option>';
    }
}elseif(!empty($_POST["state_id"])){
    //Fetch all city data
    $query = $db->query("SELECT * FROM cities WHERE state_id = ".$_POST['state_id']." AND status = 1 ORDER BY city_name ASC");
    
    //Count total number of rows
    $rowCount = $query->num_rows;
    
    //City option list
    if($rowCount > 0){
        echo '<option value="">Select city</option>';
        while($row = $query->fetch_assoc()){ 
            echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>';
        }
    }else{
        echo '<option value="">City not available</option>';
    }
}
?>

see the full tutorial in this link

</div>