retrieve data into ajax dropdown menu using PHP My

问题:

i create dynamic multiple country dropdown using Jquery/PHP/MySql. this worked fine and i store/put country/state/town for each user in MySql Database like this (usertable) :

id  |  country  | state | town |
 1  |     1     |   1   |   1  |
 2  |     1     |   1   |   3  |
 3  |     1     |   2   |   8  |

now in edituser.php page i need to fetch/retrieve MySql data (usertable) to my Jquery/Ajax dropdown. i call edituser.php?id=1 Now i need to print/show user data to dropdown Ajax for edit country/state/town user.

How Do can i retrieve/print/show this?

JS:

<script type="text/javascript" src="js/jquery-1.4.1.min.js"></script>
// jquery library file
<script type="text/javascript">

/*This function is called when state dropdown value change*/
function selectState(state_id){
  if(state_id!="-1"){
    loadData('city',state_id);
  }else{
    $("#city_dropdown").html("<option value='-1'>Select city</option>");
  }
}

/*This function is called when city dropdown value change*/
function selectCity(country_id){
 if(country_id!="-1"){
   loadData('state',country_id);
   $("#city_dropdown").html("<option value='-1'>Select city</option>");
 }else{
  $("#state_dropdown").html("<option value='-1'>Select state</option>");
   $("#city_dropdown").html("<option value='-1'>Select city</option>");
 }
}

/*This is the main content load function, and it will
     called whenever any valid dropdown value changed.*/
function loadData(loadType,loadId){
  var dataString = 'loadType='+ loadType +'&loadId='+ loadId;
  $("#"+loadType+"_loader").show();
  $("#"+loadType+"_loader").fadeIn(400).
        html('Please wait... <img src="image/loading.gif" />');
  $.ajax({
     type: "POST",
     url: "loadData.php",
     data: dataString,
     cache: false,
     success: function(result){
       $("#"+loadType+"_loader").hide();
       $("#"+loadType+"_dropdown").
       html("<option value='-1'>Select "+loadType+"</option>");
       $("#"+loadType+"_dropdown").append(result);
     }
   });
}
</script>

HTML:

/*This code will show country dropdown list*/
<select onchange="selectCity(this.options[this.selectedIndex].value)">
   <option value="-1">Select country</option>
   <?php
     while($rowCountry=mysql_fetch_array($resCountry)){
   ?>
     <option value="<?php echo $rowCountry['id']?>">
            <?php echo $rowCountry['country_name']?>
     </option>
   <?php
   }
   ?>
</select>

/*State dropdown list*/
<select id="state_dropdown"
     onchange="selectState(this.options[this.selectedIndex].value)">
<option value="-1">Select state</option>
</select>
<span id="state_loader"></span>

/*City dropdown list*/
<select id="city_dropdown">
<option value="-1">Select city</option>
</select>
<span id="city_loader"></span>

Loaddata.php

include('dbConnect.inc.php');
$loadType=$_POST['loadType'];
$loadId=$_POST['loadId'];

if($loadType=="state"){
   $sql="select id,state_name from state_test where
         country_id='".$loadId."' order by state_name asc";
}else{
   $sql="select id,city_name from city_test where
         state_id='".$loadId."' order by city_name asc";
}
$res=mysql_query($sql);
$check=mysql_num_rows($res);
if($check > 0){
   $HTML="";
   while($row=mysql_fetch_array($res)){
      $HTML.="<option value='".$row['id']."'>".$row['1']."</option>";
   }
   echo $HTML;
}

回答1:

i tink the Kendo UI dropdown widget will solve this problem. there is a cascade feature on it

回答2:

Few thinks about your code

• With AJAX, try to use JSON to send and retrieve data, it's going to give you more freedom about vars and UI.

• As you're using jQuery, try to use it as much as possible, not defining online events, if you group them in the script it'll be easier for you to manage it.

• About the select, it's quite tricky reload them. In IE I remember I couldn't add options, so, you have to load the WHOLE select.

• Don't use PHP mysql_query functions, are quite deprecated. Read and apply this: How can I prevent SQL injection in PHP?

• When loading values from AJAX, you have to attach the handler to the DOM elements, that's why using .on() function, to make sure it does attach the handler to the elements.

• Try to use the newer libraries of jQuery, as they are faster, powerful and have increased performance, 1.4 is pretty old...

I've written to you an example of dropdown of countries using above things, to give you a clue of how to achieve it, take what you think you like it:

index.html:

<html>

    <head>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8/jquery.min.js"></script>
    </head>

    <body>

    <script>
    $(document).ready(function () {
        $(document).on('change', '#div_country select, #div_state select', function () {
            var $type = $(this).attr('data-type');
            var $id = $(this).val();

            if ( $id != -1 ) {
                loadSelect( $type, $id );
            }

        });

        function loadSelect( $type, $id ) {
            $.ajax({
                type: 'post',
                url: 'states.php',
                cache: false,
                data: {
                    'type': $type
                ,   'id': $id
                },
                dataType: 'json',
                success: function (data) {
                    if ( data.result == true ) {
                        if (data.html !== undefined) {
                            var $div = '';
                            if ( $type == 'country') {
                                $div = 'state';
                            } else {
                                $div = 'city';
                            }
                            $( "#div_" + $div ).html(data.html);
                        }
                    } else {
                        alert('Something went wrong!');
                    }
                }
            });
        }

    });
    </script>

    <div id="div_country">
        <select data-type="country">
            <option value="-1">Select Country</option>
            <option value="1">Spain</option>
            <option value="2">France</option>
            <option value="3">Germany</option>
        </select>
    </div>

    <div id="div_state">
        <select data-type="state">
            <option value="-1">Select Country</option>
        </select>
    </div>

    <div id="div_city">
        <select>
            <option value="-1">Select State</option>
        </select>
    </div>

    </body>
</html>

state.php

<?php
$type = isset( $_POST['type'] ) ? $_POST['type'] : '';

if ( !empty( $type ) ) {
    switch ($type) {
        case 'country':
            $result = true;
            $html = '<select data-type="state">
                        <option value="-1">Select State</option>
                        <option value="1">state 1</option>
                        <option value="2">state 2</option>
                    </select>';
        break;
        case 'state':
            $result = true;
            $html = '<select data-type="city">
                        <option value="-1">Select City</option>
                        <option value="1">city 1</option>
                        <option value="2">city 2</option>
                    </select>';
        break;
        default:
            $result = false;
            $html = '';
        break;
    }
}

$data = array(
    'result' => $result,
    'html' => $html
);

I've added to give you a clue about how to achieve it, it's a stand alone example, and you'll see the changes of the dropbox. You'll have to add the PHP logic, but I wanted to show you a better approach, XD