php 基于 SQL 数据库的自动完成文本框结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21298169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Autocomplete Textbox results based from SQL database
提问by Logan Wayne
I'm trying to create an auto-complete function into a textbox but the result should come from my SQL database.
我正在尝试在文本框中创建一个自动完成功能，但结果应该来自我的 SQL 数据库。
Here's the code that i'm trying to configure:
index.php:
这是我尝试配置的代码：
index.php：
<html lang="en">
<head>
<meta charset="utf-8">
<title>jQuery UI Autocomplete - Default functionality</title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css">
<script>
$(function() {
var availableTags = [
"autocomplete.php"; ];
$( "#tags" ).autocomplete({
source: availableTags
});
});
</script>
</head>
<body>
<div class="ui-widget">
<label for="tags">Tags: </label>
<input id="tags">
</div>
</body>
</html>
EDIT: I changed the content of variable availableTags and made it into var availableTags = <?php include('autocomplete.php') ?>;
编辑：我更改了变量 availableTags 的内容并将其变为var availableTags = <?php include('autocomplete.php') ?>;
Variable availableTagsis the source of words, so I try to change it and instead put a file name where fetching of words from my database is happening.
变量availableTags是单词的来源，因此我尝试更改它，而是将文件名放在正在从我的数据库中获取单词的位置。
Here's my autocomplete.phpfile:
这是我的autocomplete.php文件：
<?php
include('conn.php');
$sql="SELECT * FROM oldemp";
$result = mysqli_query($mysqli,$sql) or die(mysqli_error());
while($row=mysqli_fetch_array($result))
{
echo "'".$row['name']."', ";
}
?>
EDIT: Also changed the content of the while loop and made it into
编辑：还更改了 while 循环的内容并将其变为
$name=mysqli_real_escape_string($con,$row['name']);
$json[]=$name;
How can I insert the fetched words from autocomplete.phpinto availableTagsvariable?
如何将从autocomplete.php获取的单词插入到availableTags变量中？
EDIT/UPDATE: There's a list showing up whenever I type something on the textbox, but it has no text in it. I know it's fetching, but the word itself is not showing on the list.
编辑/更新：每当我在文本框中键入内容时都会显示一个列表，但其中没有文本。我知道它很吸引人，但这个词本身没有显示在列表中。
采纳答案by Logan Wayne
Solved my problem.
解决了我的问题。
Have the script like this:
有这样的脚本：
<!-- WITHOUT THESE THREE BELOW, THE AUTOCOMPLETE WILL LOOK UGLY OR WILL NOT WORK AT ALL -->
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
<script>
$(function() {
$( "#tags" ).autocomplete({
source: "autocomplete.php"
});
});
</script>
And autocomplete.php(where we will get the data to fill the autocomplete input field):
还有autocomplete.php（我们将在其中获取数据以填充自动完成输入字段）：
<?php
include("conn.php"); /* ESTABLISH CONNECTION IN THIS FILE; MAKE SURE THAT IT IS mysqli_* */
$stmt = $con->prepare("SELECT description FROM table"); /* START PREPARED STATEMENT */
$stmt->execute(); /* EXECUTE THE QUERY */
$stmt->bind_result($description); /* BIND THE RESULT TO THIS VARIABLE */
while($stmt->fetch()){ /* FETCH ALL RESULTS */
$description_arr[] = $description; /* STORE EACH RESULT TO THIS VARIABLE IN ARRAY */
} /* END OF WHILE LOOP */
echo json_encode($description_arr); /* ECHO ALL THE RESULTS */
?>
回答by Useless Code
The jQuery UI autocomplete can take 3 different types of values of the source option:
jQuery UI 自动完成可以采用 3 种不同类型的源选项值：
- An arraycontaining the list of things to fill in the auto complete with
- A stringcontaining the URL of a script that filters a list and sends us the results. The plugin will take text typed into it and send it as a
termparameter in a query-string appended to the URL we provided. - A functionthat retrieves the data and then calls a callback with that data.
- 一个包含要填充自动完成的事物列表的数组
- 一个包含过滤列表并向我们发送结果的脚本的 URL 的字符串。该插件将输入输入的文本并将其作为
term附加到我们提供的 URL 的查询字符串中的参数发送。 - 一个函数检索数据，然后调用与数据的回调。
Your original code uses the first, an array.
您的原始代码使用第一个数组。
var availableTags = [
"autocomplete.php";
];
What that tells the autocomplete is that the string "autocomplete.php"is the only thing in the list of things to autocomplete with.
告诉自动完成的是，字符串"autocomplete.php"是要自动完成的内容列表中的唯一内容。
I think what you were trying to do is embed it with something like this:
我认为您想要做的是将其嵌入如下内容：
$(function() {
var availableTags = [
<?php include("autocomplete.php"); /* include the output of autocomplete as array data */ ?>;
];
$( "#tags" ).autocomplete({
source: availableTags
});
});
That would probably work okay assuming that the list of things that are being returned from the database will always remain short. Doing it this way is kind of fragile though since you are just shoving raw output from PHP into your JS. If the returned data contains "you might have to use addSlashesto escape it correctly. You should however change the query to return a single field rather than *, you probably only want one field as the label in the autocomplete not the entire row.
假设从数据库返回的事物列表将始终保持简短，这可能会正常工作。这样做有点脆弱，因为您只是将原始输出从 PHP 推送到您的 JS 中。如果返回的数据包含"您可能必须使用addSlashes来正确转义它。但是，您应该更改查询以返回单个字段而不是*，您可能只需要一个字段作为自动完成中的标签而不是整行。
A better approach, especially if the list could potentially grow very large, would be to use the second method:
更好的方法是使用第二种方法，尤其是当列表可能变得非常大时：
$(function() {
var availableTags = "autocomplete.php";
$( "#tags" ).autocomplete({
source: availableTags
});
});
This will require you to make a change to the back-end script that is grabbing the list so that it does the filtering. This example uses a prepared statementto ensure the user provided data in $termdoesn't open you up to SQL injection:
这将要求您对抓取列表的后端脚本进行更改，以便进行过滤。此示例使用准备好的语句来确保用户提供的数据$term不会让您受到SQL 注入的影响：
<?php
include('conn.php');
// when it calls autocomplete.php, jQuery will add a term parameter
// for us to use in filtering the data we return. The % is appended
// because we will be using the LIKE operator.
$term = $_GET['term'] . '%';
$output = array();
// the ? will be replaced with the value that was passed via the
// term parameter in the query string
$sql="SELECT name FROM oldemp WHERE name LIKE ?";
$stmt = mysqli_stmt_init($mysqli);
if (mysqli_stmt_prepare($stmt, $sql)) {
// bind the value of $term to ? in the query as a string
mysqli_stmt_bind_param($stmt, 's', $term);
mysqli_stmt_execute($stmt);
// binds $somefield to the single field returned by the query
mysqli_stmt_bind_result($stmt, $somefield);
// loop through the results and build an array.
while (mysqli_stmt_fetch($stmt)) {
// because it is bound to the result
// $somefield will change on every loop
// and have the content of that field from
// the current row.
$output[] = $somefield;
}
mysqli_stmt_close($stmt);
}
mysqli_close($mysqli);
// output our results as JSON as jQuery expects
echo json_encode($output);
?>
It's been a while since I've worked with mysqli, so that code might need some tweaking as it hasn't been tested.
自从我使用 mysqli 以来已经有一段时间了，因此该代码可能需要进行一些调整，因为它尚未经过测试。
It would be good to get into the habit of using prepared statements since when properly used, they make SQL injection impossible. You can instead use a normal non-prepared statement, escaping every user-provided item with mysqli_real_escape_stringbefore you insert it into your SQL statement. However, doing this is very error-prone. It only takes forgetting to escape one thing to open yourself up to attacks. Most of the major "hacks"in recent history are due to sloppy coding introducing SQL injection vulnerabilities.
养成使用准备好的语句的习惯会很好，因为如果使用得当，它们会使 SQL 注入成为不可能。您可以改为使用普通的非准备语句，在将每个用户提供的项目插入到 SQL 语句之前使用mysqli_real_escape_string 对其进行转义。但是，这样做非常容易出错。只需要忘记逃避一件事就可以让自己接受攻击。最近历史上的大多数主要“黑客”都是由于草率的编码引入了 SQL 注入漏洞。
If you really want to stick with the non-prepared statement, the code would look something like this:
如果你真的想坚持使用 non-prepared 语句，代码看起来像这样：
<?php
include('conn.php');
$term = $_GET['term'];
$term = mysqli_real_escape_string($mysqli, $term);
$output = array();
$sql = "SELECT name FROM oldemp WHERE name LIKE '" . $term . "%';";
$result = mysqli_query($mysqli,$sql) or die(mysqli_error());
while($row=mysqli_fetch_array($result))
{
$output[] = $row['name'];
}
mysqli_close($mysqli);
// output our results as JSON as jQuery expects
echo json_encode($output);
?>
回答by Ohgodwhy
When a string is used, the Autocomplete plugin expects that string to point to a URL resource that will return JSON data.
使用字符串时，自动完成插件期望该字符串指向将返回 JSON 数据的 URL 资源。
source: "autocomplete.php"
Therefore you need to return a JSON object.
因此，您需要返回一个 JSON 对象。
$json = false;
while($row=mysqli_fetch_array($result))
{
$json[] = array(
'name' => $row['name']
);
}
echo json_encode($json);
回答by Kamehameha
Your autocomplete.php file,
您的 autocomplete.php 文件，
include('conn.php');
$sql="SELECT * FROM oldemp";
$result = mysqli_query($mysqli,$sql) or die(mysqli_error());
//Create an array
$arr = Array();
while($row=mysqli_fetch_array($result))
{
array_push($arr,$row['name']);
}
header('Content-Type: application/json');
echo json_encode($arr)
?>
The result of this will be an JSON array which can be directly used in JavaScript. Hence, the script will be something like -
这样做的结果将是一个可以直接在 JavaScript 中使用的 JSON 数组。因此，脚本将类似于 -
var availableTags = [];
$.ajax({
url:"autocomplete.php",success:function(result){
availableTags = result
}});
回答by Darshan Montgomery
Just a suggestion for the autocomplete file. Sorry, I would have added a comment above, but I don't have enough rep as of writing this.
只是对自动完成文件的建议。抱歉，我会在上面添加评论，但在撰写本文时我没有足够的代表。
After successfully implementing Useless Code's suggestion I was noticing my server overhead was going through the roof. It seemed bots were some how initiating the script, even though there was no letters being typed in the input area. I did a little test on the autocomplete file and found it would query my database even if the term was empty.
在成功实施无用代码的建议后，我注意到我的服务器开销正在飙升。似乎机器人是如何启动脚本的，即使输入区域中没有输入字母。我对自动完成文件进行了一些测试，发现即使该术语为空，它也会查询我的数据库。
So, I just encpsulated the whole autocomplete script with an if statement... like so...
所以，我只是用 if 语句封装了整个自动完成脚本......就像这样......
<?php
if(!empty($_GET['term']))
{
include('conn.php');
$term = $_GET['term'];
$term = mysqli_real_escape_string($mysqli, $term);
$output = array();
$sql = "SELECT name FROM oldemp WHERE name LIKE '" . $term . "%';";
$result = mysqli_query($mysqli,$sql) or die(mysqli_error());
while($row=mysqli_fetch_array($result))
{
$output[] = $row['name'];
}
mysqli_close($mysqli);
// output our results as JSON as jQuery expects
echo json_encode($output);
}
?>
... and now my server is back to normal.
...现在我的服务器恢复正常。
