jQuery自动完成和PHP:根据自动完成字段中的选定选项，使用来自mySQL数据库的数据填充输入字段 [英] Jquery autocomplete and PHP: populating input field with data from mySQL database based on selected option in autocomplete field
问题描述
我正在尝试根据用户从jQuery自动完成字段中选择的Suburbs选项，使用mySQL数据库中的数据填充邮政编码(即邮政编码)输入字段.
I am trying to populate a Postcode (ie zipcode) input field with data from a mySQL database, based on the user's selected option of Suburbs from a jQuery autocomplete field.
自动完成功能可以正常工作-根据用户输入的条件检索已过滤的郊区列表.源引用是一个PHP文件.但是我不知道如何使用用户的选定选项来回调数据库以检索邮政编码.可能可以在第一个电话中与郊区同时检索邮政编码:除非我不希望所有邮政编码，而只是用户最终选择的邮政编码.
The autocomplete works fine - the filtered Suburbs list is retrieved based on the input terms from the user. The source reference is a PHP file. But I can't figure out how to then use the user's selected option to call back to the database to retrieve the postcode. Possibly the postcode can be retrieved in the first call, at the same time as the suburbs: Except I don't want all of the postcodes, just the one that the user ends up selecting.
我的jQuery如下:("$('#postcodes')"行目前尚不起作用...)
My jQuery is as follows: (The "$('#postcodes')" line doesn't work as yet...)
<script type="text/javascript" src="js/jquery-1.6.2.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.8.15.custom.min.js"></script>
<script>
// autocomplete
$(function() {
$( "#suburbs" ).autocomplete({
source: "allSuburbs.php",
minLength: 3,
select: function( event, ui ) {
$('#postcodes').val(ui.item.postcode);
},
});
});
</script>
相关html:
<p>Suburb</p><input class="inputText" type="text"
size="50" name="term" id="suburbs" maxlength="60" /></td>
<td><p>State</p><input class="inputText" type="text"
size="5" name="" id="states" maxlength="4" /></td>
<td><p>Postcode</p><input class="inputText" type="text"
size="5" name="" id="postcodes" maxlength="4" /></td>
PHP(allSuburbs.php):
PHP (allSuburbs.php):
<?php
$con = mysql_connect("***","***","***");
if (!$con) { die('Could not connect: ' . mysql_error()); }
$dbname = 'suburb_state';
mysql_select_db($dbname);
$query = "SELECT name FROM suburbs";
$result = mysql_query($query);
if (!$result) die ("Database access failed:" . mysql_error());
//retrieving the search term that autocomplete sends
$qstring = "SELECT name FROM suburbs WHERE name LIKE '%".$term."%'";
//query the database for entries containing the term
$result = mysql_query($qstring);
//loop through the retrieved values
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{ $row['name']=htmlentities(stripslashes($row['name']));
$row['postcode']=htmlentities(stripslashes($row['postcode']));
$row_set[] = $row['name'];//build an array
}
echo json_encode($row_set);//format the array into json data
mysql_close($con);
?>
我发现这些链接可能最有用:
I've found thse links possibly the most helpful:
http://www .simonbattersby.com/blog/jquery-ui-autocomplete-with-a-remote-database-and-php/ (最初对我有帮助)
http://www.simonbattersby.com/blog/jquery-ui-autocomplete-with-a-remote-database-and-php/ (This helped me initially)
http://www.jensbits.com/2010/05/29/using-jquery-autocomplete-to-populate-another-autocomplete-asp-net-coldfusion-and-php-examples/ (this is the closest to my problem, although it populates the zipcode or postcode field with a range of zipcodes based on a state selection, rather than a single zipcode based on one suburb/city).
任何帮助表示赞赏. 非常感谢你， 安德鲁
Any help appreciated. Thank you kindly, Andrew
推荐答案
我已经将该功能完全 内置到了我的应用程序中.这里有一个额外的复杂性层，因为有两个郊区查找(家庭和工作地址)，每个查找都填充匹配的州和邮政编码字段.后端是perl而不是PHP，但这与客户端处理无关.最终，后端将返回带有以下哈希表数组的JSON结构:
I have built exactly this function into an app of mine. There is an additional layer of complexity here, in that there are two suburb lookups (home and work addresses), each populating matching state and postcode fields. The back-end is perl rather than PHP, but that's not relevant to the client-side handling. Ultimately the back-end is returning a JSON structure with an array of hashes like this:
[ { "id":"...", "value":"...", "state":"...", "pcode":"..." }, ... ]
id键包含郊区名称，而value键包含诸如"JOLIET IL 60403"之类的字符串，因此，一次选择了正确的 set 数据，从而解决了具有多个城镇/郊区的问题在不同的地方使用相同的名称，并进行回叫以解决该问题.
The id key contains the suburb name, and the value key contains strings like "JOLIET IL 60403", so the correct set of data is chosen once, solving the problem of multiple towns/suburbs with the same name in different places, and making call-backs to resolve that.
选择后，将郊区(id)，州和pcode值注入匹配的参数中.
Once selected, the suburb (id), state and pcode values are injected into the matching parameters.
以下代码还缓存了以前的结果(并且缓存在家庭和工作查找之间共享).
The following code also caches previous results (and the cache is shared between the home and work lookups).
$('#hm_suburb').addClass('suburb_search').attr(
{suburb: '#hm_suburb', pcode: '#hm_pcode', state: '#hm_state'});
$('#wk_suburb').addClass('suburb_search').attr(
{suburb: '#wk_suburb', pcode: '#wk_pcode', state: '#wk_state'});
var sub_cache = {};
$(".suburb_search").autocomplete({
source: function(request, response) {
if (request.term in sub_cache) {
response($.map(sub_cache[request.term], function(item) {
return { value: item.value, id: item.id,
state: item.state, pcode: item.pcode }
}))
return;
}
$.ajax({
url: suburb_url,
data: "term=" + request.term,
dataType: "json",
type: "GET",
contentType: "application/json; charset=utf-8",
dataFilter: function(data) { return data; },
success: function(data) {
sub_cache[request.term] = data;
response($.map(data, function(item) {
return {
value: item.value,
id: item.id,
state: item.state,
pcode: item.pcode
}
}))
} //,
//error: HandleAjaxError // custom method
});
},
minLength: 3,
select: function(event, ui) {
if (ui.item) {
$this = $(this);
//alert("this suburb field = " + $this.attr('suburb'));
$($this.attr('suburb')).val(ui.item.id);
$($this.attr('pcode')).val(ui.item.pcode);
$($this.attr('state')).val(ui.item.state);
event.preventDefault();
}
}
});
这篇关于jQuery自动完成和PHP:根据自动完成字段中的选定选项，使用来自mySQL数据库的数据填充输入字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
