php - 如何使用 PHP + MySQL Ajax 获取 jQuery 自动完成功能并传递给 JavaScript

Question

我有一个用于自动完成搜索功能的 javascript 代码，它工作正常。但是如果您查看下面的代码，搜索功能返回的数据是硬编码的，我想使用 PHP 从 MySQL 获取数据。

任何人都可以帮助我如何将下面的代码转换为使用 PHP 查询来收集数据 MySQL 然后使用结果并将其传递给 javascript？谢谢。

//<![CDATA[
var a1;
var a2;

function InitMonths() {
    a2.setOptions({ lookup: 'January,February,March,April,May,June,July,August,September,October,November,December'.split(',') });
}

function InitWeekdays() {
    a2.setOptions({ lookup: 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday'.split(',') });
}

jQuery(function () {

    a1 = $('#query').autocomplete({
        width: 448,
        delimiter: /(,|;)\s*/,
        lookup: 'Andorra,Azerbaijan,Bahamas,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin,Bhutan,Bolivia,Bosnia Herzegovina,Botswana,Brazil,Brunei,Bulgaria,Burkina,etc'.split(',')
    });

    a2 = $('#months').autocomplete({
        width: 448,
        delimiter: /(,|;)\s*/,
        lookup: 'January,February,March,April,May,etc'.split(',')
    });

    $('#navigation a').each(function () {
        $(this).click(function (e) {
            var element = $(this).attr('href');
            $('html').animate({ scrollTop: $(element).offset().top }, 300, null, function () { document.location = element; });
            e.preventDefault();
        });
    });

});

Answer 1

新

基于这个自动完成插件 http://www.devbridge.com/projects/autocomplete/jquery/

您的 JavasSript 需要看起来像这样:

//Start auto complete
a1 = $("#query").autocomplete({
    serviceUrl:'search.php', //tell the script where to send requests
    width: 448, //set width

    //callback just to show it's working
    onSelect: function(value, data){ alert('You selected: ' + value + ', ' + data); } 
});

您的 PHP (search.php) 需要:

///
/*** connect to your DB here ***/
///

//retrieve the search term and strip and clean input
$term = trim(strip_tags($_GET['query'])); 

//try to make user input safer
$term = mysqli_real_escape_string($term);

//build a query on the database
$qstring = "SELECT description as value,id FROM test WHERE description LIKE '%".$term."%'";

//query the database for entries containing the term
$result = mysql_query($qstring);

//array to return
$reply = array();
$reply['query'] = $term;
$reply['suggestions'] = array();
$reply['data'] = array();

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values
{
    //Add this row to the reply
    $reply['suggestions'][]=htmlentities(stripslashes($row['value']));
    $reply['data'][]=(int)$row['id'];
}

//format the array into json data
echo json_encode($reply);

插件需要像下面这个 PHP 应该提供的 json

query:'Li',
suggestions:['Liberia','Libyan Arab Jamahiriya','Liechtenstein','Lithuania'],
data:['LR','LY','LI','LT']

请注意，我还没有对此进行测试，但它应该没问题!

---

旧答案

参见此处:http://www.simonbattersby.com/blog/jquery-ui-autocomplete-with-a-remote-database-and-php/

首先，如果您不使用 jQuery 自动完成插件(jQuery 支持的插件，作为 jQuery UI 的一部分)，请进行设置。 http://jqueryui.com/demos/autocomplete/#remote

你问的是如何彻底改造系统。

首先，您需要使用 Ajax 通过 PHP 作为代理将匹配字符串发送到数据库，然后 PHP 需要返回结果并让 Javascript 读取它们。

所以你需要使用(作为配置):

a1 = $("#query").autocomplete({
source: "search.php"
width: 448  
});

还有类似的东西(作为你的 PHP)

//connect to your database

$term = trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends

$qstring = "SELECT description as value,id FROM test WHERE description LIKE '%".$term."%'";
$result = mysql_query($qstring);//query the database for entries containing the term

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values
{
        $row['value']=htmlentities(stripslashes($row['value']));
        $row['id']=(int)$row['id'];
        $row_set[] = $row;//build an array
}
echo json_encode($row_set);//format the array into json data