Multiple selection listbox of State not displaying all Cities in another listbox(Php,mysql,ajax)

Question

Selecting the Country dropdown displays State listbox with multiple selections without refreshing page . But selecting 2 or 3 States not displaying all cities in another lisbox. Cities are displaying for only one State. help

loadData.php

<?php
$q = $_GET['q'];
$con = mysqli_connect('localhost','root','','test');
if (!$con) {
  die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"test");
$sql="SELECT * FROM state WHERE country_id = '".$q."'";
$result = mysqli_query($con,$sql);
    echo "<select name='try[]' onchange='showSecondUser(this.value)' multiple>";
while($row = mysqli_fetch_array($result)){
          echo "<option value='".$row['id']."'>".$row['state_name']."</option>";
}
    echo" </select>";



$g = $_GET['g'];    
$sql="SELECT * FROM city WHERE state_id = '".$g."'";
$result = mysqli_query($con,$sql);
    echo "<select name='try' multiple>";
while($row = mysqli_fetch_array($result)){
          echo "<option value=''>".$row['city_name']."</option>";
}
    echo" </select>";

mysqli_close($con);
?>

index.php

<html>
<head>
<script>
function showUser(str) {
  if (str=="") {
    document.getElementById("txtHint").innerHTML="";
    return;
  }
  if (window.XMLHttpRequest) {
    // code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
  } else { // code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
  xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState==4 && xmlhttp.status==200) {
      document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
  xmlhttp.open("GET","loadData.php?q="+str,true);
  xmlhttp.send();
}


function showSecondUser(str) {
  if (str=="") {
    document.getElementById("txtHint2").innerHTML="";
    return;
  }
  if (window.XMLHttpRequest) {
    // code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
  } else { // code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
  xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState==4 && xmlhttp.status==200) {
      document.getElementById("txtHint2").innerHTML=xmlhttp.responseText;
    }
  }
  xmlhttp.open("GET","loadData.php?g="+str,true);
  xmlhttp.send();
}



</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select Country</option>
<option value="1">Australia</option>
<option value="2">Japan</option>
<option value="3">Russia</option>
<option value="4">Germany</option>
</select>
</form>
<br>
<div id="txtHint"></div>

<div id="txtHint2"></div>

</body>
</html>

Laci K · Accepted Answer · 2014-10-13 00:11:41Z

1

It' because mysql query is wrong. You providing multiple numbers in a simple equal.

That should be:

$g = $_GET['g'];    
$sql="SELECT * FROM city WHERE state_id IN (".$g.")";
$result = mysqli_query($con,$sql);

if $g is a comma separeted list of numbers. If not you have to make it so.

So if $g is an array you have to do an implode() on it and than you can use it in the query

$g = $_GET['g'];    
$g = implode(',', $g);
$sql="SELECT * FROM city WHERE state_id IN (".$g.")";
$result = mysqli_query($con,$sql);

if it is a string and lets say the numbers are separated my space than you replace space with comma:

$g = $_GET['g'];    
$g = str_replace(' ', ',', $g);
$sql="SELECT * FROM city WHERE state_id IN (".$g.")";
$result = mysqli_query($con,$sql);

So in your case the MySQL query should be this:

$sql="SELECT * FROM state WHERE country_id = '".$q."'";
$result = mysqli_query($con,$sql);
    echo "<select name='try[]' onchange='showSecondUser(this)' multiple>";
    //                            there is a change here ^
    while($row = mysqli_fetch_array($result)){
       echo "<option value='".$row['id']."'>".$row['state_name']."</option>";
    }
    echo" </select>";
/*######################################################*/
$g = $_GET['g'];    
$sql="SELECT * FROM city WHERE state_id IN (".$g.")";
$result = mysqli_query($con,$sql);

And the javascript should be this:

function showSecondUser(str){
    var xmlhttp;
    if (window.XMLHttpRequest) {
        xmlhttp = new XMLHttpRequest();
    } else {
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange = function () {
        if (xmlhttp.readyState === 4 && xmlhttp.status === 200) {
            document.getElementById("txtHint2").innerHTML = xmlhttp.responseText;
        }
    }

    var values = new Array();
    for (var i=0; i < str.options.length; i++) {
        cur = sel.options[i];
        if (cur.selected) {
            values.push(cur.value);
        }
    }
    if (values.length) {
        values = values.join(",");
    } else {
        values = null;
    }

    xmlhttp.open("GET","loadData.php?g="+values,true);
    xmlhttp.send();
}

edited Oct 13, 2014 at 0:11

answered Oct 10, 2014 at 19:45

Laci K

5952 gold badges9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

vas_bar_code Over a year ago

how to apply $g value in select query if $g has three values

Laci K Over a year ago

Is $g an array or string? if string how are the numbers separated in it?

vas_bar_code Over a year ago

$g is the value, I am getting from index.php page from this line. xmlhttp.open("GET","loadData.php?g="+str,true); If I am getting single value in $g,how to get multiple values?

Laci K Over a year ago

Ok thats because your call will only get the first value from the select list. You have to loop through the select list to get all selected value.

Laci K Over a year ago

I added an example for you

|

Collectives™ on Stack Overflow

Multiple selection listbox of State not displaying all Cities in another listbox(Php,mysql,ajax)

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related