使用PHP MySql jQuery将数据检索到ajax下拉菜单中 [英] retrieve data into ajax dropdown menu using PHP MySql jQuery
问题描述
我使用 Jquery / PHP / MySql 创建动态多国国家/地区下拉列表。对于MySql数据库中的每个用户，这个工作正常，我（usertable） ：
id |国家|州|镇|
1 | 1 | 1 | 1 |
2 | 1 | 1 | 3 |
3 | 1 | 2 | 8 |
现在在 edituser.php 页面我需要到我的Jquery / Ajax下拉菜单中的 fetch / retrieve MySql数据（usertable）我调用 edituser.php？id = 1 现在，我需要打印/显示用户数据到下拉列表Ajax 为编辑国家/州/镇用户。
我可以如何检索/打印/显示？
JS：
< script type =text / javascriptsrc =js / jquery-1.4.1.min.js> ;< /脚本>
// jquery库文件
< script type =text / javascript>
/ *当状态下拉值更改时调用此函数* /
函数selectState（state_id）{
if（state_id！= - 1）{
loadData（ '城市'，STATE_ID）;
} else {
$（＃city_dropdown）。html（< option value =' - 1'>选择城市< / option>）;
}
}
/ *当城市下拉值更改时，会调用此函数* /
函数selectCity（country_id）{
if（country_id！= -1）{
loadData（'state'，country_id）;
$（＃city_dropdown）。html（< option value =' - 1'>选择城市< / option>）;
} else {
$（＃state_dropdown）。html（< option value =' - 1'>选择状态< / option>）;
$（＃city_dropdown）。html（< option value =' - 1'>选择城市< / option>）;
}
}
/ *这是主要的内容加载功能，每当任何有效的下拉列表值更改时，
将被调用* /
函数loadData（loadType，loadId）{
var dataString ='loadType ='+ loadType +'& loadId ='+ loadId;
$（＃+ loadType +_ loader）。show（）;
$（＃+ loadType +_ loader）。fadeIn（400）。
html（'Please wait ...< img src =image / load.gif/>'）;
$ .ajax（{
type：POST，
url：loadData.php，
data：dataString，
cache：false，
成功：function（result）{
$（＃+ loadType +_ loader）hide（）;
$（＃+ loadType +_ dropdown）
html < option value =' - 1'>选择+ loadType +< / option>）;
$（＃+ loadType +_ _ dropdown）。append（result）;
}
}）;
}
< / script>
HTML：
code> / *此代码将显示国家/地区的下拉列表* /
< select onchange =selectCity（this.options [this.selectedIndex] .value）>
< option value = - 1>选择国家< / option>
<？php
while（$ rowCountry = mysql_fetch_array（$ resCountry））{
？>
< option value =<？php echo $ rowCountry ['id']？>>
<？php echo $ rowCountry ['country_name']？>
< / option>
<？php
}
？>
< / select>
/ *状态下拉列表* /
< select id =state_dropdown
onchange =selectState（this.options [this.selectedIndex] .value）>
< option value = - 1>选择状态< / option>
< / select>
< span id =state_loader>< / span>
/ *城市下拉列表* /
< select id =city_dropdown>
< option value = - 1>选择城市< / option>
< / select>
< span id =city_loader>< / span>
Loaddata.php
包括（ '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 ='。$ loadIdorder 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'] < /选项> 中。
}
echo $ HTML;
}
/ p>
-
使用AJAX，尝试使用JSON发送和检索数据，这将为您提供有关vars和UI的更多自由。 / p>
-
当您使用jQuery时，尽量使用它，而不是定义在线事件，如果将它们分组在脚本中将更容易对于您来管理它。
-
关于选择，重新加载它们非常棘手。在IE中，我记得我无法添加选项，所以你必须加载全部选择。
-
不要使用PHP mysql_query函数，相当不赞成。阅读并应用此：如何防止SQL注入PHP？
-
当从AJAX加载值时，必须将处理程序附加到DOM元素，这就是为什么使用.on（）函数，确保它附加了元素的处理程序。
-
尝试使用较新的jQuery库，因为它们更快，更强大，性能更高， 1.4很旧...
我已经向您写了一个使用上述内容的国家下拉列表的例子，给你一个如何实现它的线索，拿你想要的东西：
index.html：
< html>
< head>
< script type =text / javascriptsrc =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）;
}
}）;
函数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 wrong wrong！'）;
}
}
}）;
}
}）;
< / script>
< div id =div_country>
< select data-type =country>
< option value = - 1>选择国家< / option>
< option value =1>西班牙< / option>
< option value =2>法国< / option>
< option value =3>德国< / option>
< / select>
< / div>
< div id =div_state>
< select data-type =state>
< option value = - 1>选择国家< / option>
< / select>
< / div>
< div id =div_city>
< select>
< option value = - 1>选择状态< / option>
< / select>
< / div>
< / body>
< / html>
state.php
$ pre>
<？php
$ type = isset（$ _POST ['type']）？ $ _POST ['type']：'';
if（！empty（$ type））{
switch（$ type）{
case'country'：
$ result = true;
$ html ='< select data-type =state>
< option value = - 1>选择状态< / option>
< option value =1>状态1< / option>
< option value =2> state 2< / option>
< / select>';
break;
case'state'：
$ result = true;
$ html ='< select data-type =city>
< option value = - 1>选择城市< / option>
< option value =1> city 1< / option>
< option value =2> city 2< / option>
< / select>';
break;
默认值：
$ result = false;
$ html ='';
break;
}
}
$ data = array（
'result'=> $ result，
'html'=> $ html
）;
我已经添加了一个关于如何实现它的线索，这是一个独立的例子，您将看到dropbox的更改。你必须添加PHP逻辑，但是我想给你一个更好的方法，XD
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;
}
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
这篇关于使用PHP MySql jQuery将数据检索到ajax下拉菜单中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
