how to hold values of two dynamic dependent select boxes in php , mysqli

Question

i am building new web application called a store locator for this i need to filter my sql data containing in single table through dependent select boxes and display address of store in table . the problem is that i cant able hold values of two select boxes at same time for sql query

this is my index.php code

    <?php
require 'dbconn/dbconfig.php';
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">

<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1"> 
<meta name="description" content="">
    <title>store SEARCH </title>
</head>
<body>
<form name="form1" action="" method="post">

<table>

<tr>
    <td>Select store</td>
    <td>
        <select id="storeid" onchange="change_store()">
            <option>Select store</option>
            <?php
            $res=mysqli_query($db,"SELECT DISTINCT `STORE` FROM `master` ORDER BY `STORE` ASC");
            while ($row=mysqli_fetch_array($res)) {
                ?>
                <option value="<?php echo $row['STORE']; ?>"><?php echo $row['STORE'];?></option>
                <?php



            }
            ?>

        </select>
    </td>
</tr>
<tr>
    <td>Select State</td>
    <td>
        <div onchange="change_state()">
        <select id="statedd" >
            <option>select State</option>

        </select>

        </div>
    </td>
</tr>
<tr>
    <td>Select District </td>
    <td>
        <div onchange="change_district()">
        <select id="districtdd">
            <option>select District</option>
        </select>

        </div>
    </td>
</tr>
<tr>
    <td>Select City </td>
    <td>
        <div onchange="change_city()">
        <select id="citydd">
            <option>select City</option>
        </select>

        </div>
    </td>
</tr>
<tr>
    <td>Select Branch </td>
    <td>
        <div>
        <select id="branchdd">
            <option>select Branch</option>
        </select>

        </div>
    </td>
</tr>
</table>

</form>




<script type="text/javascript">
    function change_store() {
        var xmlhttp=new XMLHttpRequest();
        xmlhttp.open("GET","ajax.php?store="+document.getElementById("storeid").value,false);

        xmlhttp.send(null);

        document.getElementById("statedd").innerHTML=xmlhttp.responseText;


    }
    function change_state() {
        var xmlhttp=new XMLHttpRequest();
        xmlhttp.open("GET","ajax.php?state="+document.getElementById("stateid").value,false);

        xmlhttp.send(null);

        document.getElementById("districtdd").innerHTML=xmlhttp.responseText;
        alert(xmlhttp.responseText);

    }
    function change_district() {
        var xmlhttp=new XMLHttpRequest();
        xmlhttp.open("GET","ajax.php?district="+document.getElementById("districtid").value,false);

        xmlhttp.send(null);

        document.getElementById("citydd").innerHTML=xmlhttp.responseText;


    }
    function change_city() {
        var xmlhttp=new XMLHttpRequest();
        xmlhttp.open("GET","ajax.php?city="+document.getElementById("cityid").value,false);

        xmlhttp.send(null);

        document.getElementById("branchdd").innerHTML=xmlhttp.responseText;


    }


</script>
</body>
</html>

This is my ajax.php

    <?php
include 'dbconn/dbconfig.php';
?>
<?php
$store=$_GET["store"];
$state=$_GET["state"];

if($store!='')
{
$res=mysqli_query($db, "SELECT DISTINCT `STATE` FROM `master` WHERE `STORE`='$store' ORDER BY `STATE` ASC");
echo "<select id='stateid' onchange='change_state()'>";
while ($row=mysqli_fetch_array($res)) {


    echo "<option value=".$row['STATE'].">".$row['STATE']."</option>";
    }
echo "</select>";
}

if($state!='')
{
$res=mysqli_query($db, "SELECT DISTINCT `DISTRICT` FROM `master` WHERE `STATE` LIKE '$state%' AND `STORE`='$store' ORDER BY `DISTRICT` ASC");
echo "<select id='districtid' onchange='change_district()'>";
while ($row=mysqli_fetch_array($res)) {
    echo "<option value=".$row['DISTRICT'].">".$row["DISTRICT"]."</option>";

}
echo "</select>";

}

?>

This is my output

while changing state it doesn't processing list districts in select boxes console showing an error is

Notice: Undefined index: store in C:\xampp\htdocs\ifsctech\ajax.php on line 5

problem is that the selected value of store is not able to use for next query to process. how to hold and send dynamic values of two select boxes to process query please help

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, Jul 10 '17 at 14:49
It seems there is an mismatch between the id that in the html file, and when you are replacing it with the ajax content. In index.php the state select drop down has id — N3WOS, Jul 11 '17 at 14:40

score 0 · Answer 1 · answered Aug 08 '17 at 13:58

This seems to be the problem

xmlhttp.open("GET","ajax.php?store="+document.getElementById("storeid").value,false);

Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience. For more help, check https://xhr.spec.whatwg.org/.

score 0 · Accepted Answer · answered Aug 13 '17 at 07:28

Even I was struggling with the same problem. What I found might be helpful for you as well. I will share what worked for me. You can refer to it.

<?php
//index.php
function load_state()
{
    $connect = mysqli_connect("localhost", "root", "","state_city");
    $output = '';
    $sql = "SELECT * FROM state ORDER BY state_name";
    $result = mysqli_query($connect, $sql);
    while($row = mysqli_fetch_array($result))
        {
            $output .= '<option value="'.$row["state_id"].'">'.$row["state_name"].'</option>'; 
        }
        return $output;
}
?>
<!DOCTYPE html>
<html>
 <head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/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>-->
 </head>
 <body>
  <br /><br />
  <div class="container" style="width:600px;">
   <h2 align="center">Dependent Drop-Down using Ajax - for Testing</h2><br /><br />
   <select name="state" id="state" class="form-control action">
    <option value="">Select State</option>
    <?php echo load_state(); ?>
   </select>
   <br />
   <select name="city" id="city" class="form-control">
    <option value="">Select City</option>
   </select>
  </div>
 </body>
</html>

<script>  
$(document).ready(function(){               
  $('#state').change(function(){  
       var state_id = $(this).val();  
       $.ajax({  
            url:"fetch.php",  
            method:"POST",  
            data:{stateId:state_id},
            dataType:"text",
            success:function(data)
            {  
                 $('#city').html(data);  
            }  
       });  
    });  
});
</script>

PHP Code

<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "state_city");
$output = '';
$sql = "SELECT * FROM city WHERE state_id = '".$_POST["stateId"]."' ORDER BY city_name";
$result = mysqli_query($connect, $sql);
$output = '<option value="">Select City</option>';
while($row = mysqli_fetch_array($result))
{
    $output .= '<option value="'.$row["city_id"].'">'.$row["city_name"].'</option>';
}
echo $output;
?>

Some points to consider:

For localhost, your database should have all dependent tables (db=state_city and tb=state, tb=city in my example). It won't work if you have different databases on localhost.
However, this relation works on a Apache Hosting Server on different databases as well.
tb = state [columns = state_id, state_name] => Primary key: state_id
tb = city [columns = city_id, state_id, city_name] => Primary key:
city_id

how to hold values of two dynamic dependent select boxes in php , mysqli

2 Answers2