how to fetch multiple tables value of database into dynamic select box

Question

here i'm trying to fetch data into combobox from database in which i have 10 table. i have two combo box, in first box i called option from database which will be the first table and the option like a, b, c, d. Now when i'm selecting 'a' in first box then it should called the corresponding value through php and that value will be come from another table and there is nothing common key in both of them tables. Same as when selecting 'b' then it will call the another value from the 3rd table.

So here i was trying with the if else condition but i'm able get output . somebody please improve jquery mistake , in which way i should match the condition of it .

here's my code html page

    <html>
    <body>
    <select id="a1_title">
    </select>
    <select id="a2_title">
    </select> 
    <script src="jquery-2.0.3.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
      $.getJSON("drpdwn.php", success = function(data){
    var items="";
        for(var i = 0; i< data.length; i++){
           items +="<option value='"+ data[i].toLowerCase() +"'>" + data[i] +"</option>";
        }
        $("#a1_title").append(items); 
        $("#a1_title").change();
      });
   $("#a1_title").change(function(){
    if( data.length == 1) // here i have tried to match the condition from ID but it doesn't work 
{
    alert("b");
      $.getJSON("lulc_db.php",success = function(data){
    alert("okay");
       var items="";
       for(var i = 0; i< data.length; i++){
          items+="<option value='"+data[i].toLowerCase()+"'>"+data[i]+"</option>";
       }
 else   if( data.length == 2) 
{
    alert("b");
      $.getJSON("soil_db.php",success = function(data){
    alert("okay");
       var items="";
       for(var i = 0; i< data.length; i++){
          items+="<option value='"+data[i].toLowerCase()+"'>"+data[i]+"</option>";
       }
        $("#a2_title").append(items); 

      });
     }
    });

});
 </script>
 </body></html>

This is drpdown.php page .This page value will successfully coming in first select box.

<?php
$host = "localhost"; 
$user = "postgres"; 
$pass = "admin"; 
$db = "Querybuilderdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 
$query = "SELECT id, name FROM tab";
$result = pg_query($con, $query);
if(pg_num_rows($result)){

$data=array();
while($row=pg_fetch_array($result))
{ array_push($data, $row["name"]);

}

echo  json_encode($data);
}
?>

Here the lulc_db.php .This page value should come in second select box.Same like this i have 8 other php page ,which i have show into second combobox.

    <?php 
require "config.php";
$query = "SELECT distinct level_1 FROM pachgaon_LULC";
$result = pg_query($con, $query);
if(pg_num_rows($result)){
$data=array();
while($row=pg_fetch_array($result))
{ array_push($data, $row["level_1"]);
}
echo  json_encode($data);
}
?>

Thanks in advanced :)

You might need nested `for` loop. See this [post](http://stackoverflow.com/questions/800593/loop-through-json-object-list) on looping through json. Consider key/value pairs in js object: `$.each(data, function(key, val) {...})` — Parfait, Feb 21 '16 at 06:22
i can't apply loop because the result i wanted that only can happen by selecting the value of first box and then it will call php page , and fetch the result into second combo box. — ads, Feb 21 '16 at 07:18
get some idea from answer for the question . http://stackoverflow.com/questions/11451150/create-dynamic-combobox-using-php-and-onselection-change-values-of-another-dynam .hope this helps.. :) — aimme, Feb 21 '16 at 09:14

score 1 · Accepted Answer · answered Feb 21 '16 at 18:05

Consider several changes:

Run nested for loops as one child level json (key/value pair) becomes a multi-dimensional javascript array
Capture the selected value of first box: #a1_title
Conditionally relate boxes for the dynamic output
Remove existing items before appending new items

While I cannot re-create your example without data and table relationships. Below is a generalized example using programming languages trying to maintain your initial code. Adjust for your actual script. See demo on jsfiddle (but this uses embedded json strings with click events):

PHP Scripts (to reproduce json data)

// DynamicOptions1.php
<?php
  $languages = [];

  $languages[][1] = "general purpose";
  $languages[][2] = "special purpose";

  echo json_encode($languages);
?>

// DynamicOptions2.php
<?php
  $general = [];

  $general[][1] = "Java";
  $general[][2] = "PHP";
  $general[][3] = "Perl";

  echo json_encode($general);
?>

// DynamicOptions3.php
<?php
  $special = [];

  $special[][1] = "SQL";
  $special[][2] = "XSLT";  

  echo json_encode($special);
?>

HTML/JQuery

<html>
  <body>
    <select id="a1_title">
    </select>
    <select id="a2_title">
    </select>

    <script src="jquery-2.0.3.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $.getJSON("DynamicOptions1.php", success = function(data){
            var items="";

            for(var i = 0; i< data.length; i++){
              for (var item in data[i]) {
                // RETAIN JSON KEY AS OPTION VALUE, JSON VALUE AS OPTION TEXT
                items +="<option value='"+item+"'>" + data[i][item].toLowerCase() +"</option>";
              }
            }
            $("#a1_title").append(items); 
            $("#a1_title").change();
        });

        $("#a1_title").change(function(){
          // OBTAIN SELECTED VALUE
          var selectedValue = $(this).find(":selected").val();

          if( selectedValue == "1") {              
              $.getJSON("DynamicOptions2.php",success = function(data){              
                  var items="";
                  for(var i = 0; i< data.length; i++){
                    for (var item in data[i]) {                      
                      items+="<option value='"+data[i][item]+"'>" + data[i][item].toLowerCase() +"</option>";
                    }
                  }
                  // REMOVE PREVIOUS ITEMS
                  var myNode = document.getElementById("a2_title");
                  while (myNode.firstChild) {
                      myNode.removeChild(myNode.firstChild);
                  }
                  // ADD NEW ITEMS
                  $("#a2_title").append(items);  
              });
          }
          else if( selectedValue == "2") {              
              $.getJSON("DynamicOptions3.php",success = function(data){              
                  var items="";
                  for(var i = 0; i< data.length; i++){
                    for (var item in data[i]) {
                      items+="<option value='"+data[i][item].toLowerCase()+"'>"+data[i][item].toLowerCase()+"</option>";
                    }
                  }
                  // REMOVE PREVIOUS ITEMS
                  var myNode = document.getElementById("a2_title");
                  while (myNode.firstChild) {
                      myNode.removeChild(myNode.firstChild);
                  }
                  // ADD NEW ITEMS
                  $("#a2_title").append(items);       
              });
          }          
        });

      });
      </script>

 </body>
</html>

This code contain some problem. its showing value but gives repetition of every option like bbbbbbbbbbbbbbbbb , cccccc and doesn't changing the value of second combo box when i'm selecting another option of first box. — ads, Feb 22 '16 at 06:36
Please post your json or sql data and the related rule/logic between select options (i.e., if category 1 selected in first dropdown, show these items in second dropdown). — Parfait, Feb 22 '16 at 15:05
thanks but its working now i only need to remove that " for (var item in data[i])" because of this its gives the multiple values. :) — ads, Feb 22 '16 at 15:17
Interesting! Great to hear. It all depends on the structure of json output. — Parfait, Feb 22 '16 at 18:01

how to fetch multiple tables value of database into dynamic select box

1 Answers1