problem with jquery autocomplete and mySql
search.php
$text = $mysqli->$_POST['term'];
$query = "SELECT name FROM males WHERE name LIK开发者_C百科E '%" . $text . "%' ORDER BY name ASC";
$result = $mysqli->query($query);
$json = '[';
$first = true;
while($row = $result->fetch_assoc())
{
if (!$first) { $json .= ','; } else { $first = false; }
$json .= '{"value":"'.$row['name'].'"}';
}
$json .= ']';
echo $json;
index.php
1) HTML
<body>
Text: <input type="text" id="autocomplete" />
</body>
2) jQuery
$( "#autocomplete" ).autocomplete({
source: function(request, response) {
$.ajax({ url: "http://localhost/testing/auto/search.php",
data: { term: $("#autocomplete").val()},
dataType: "json",
type: "POST",
success: function(data){
response(data);
}
});
},
minLength: 2
});
When I type 2 letters, it gives me all the names in my database even if these two letters do not match any of the names.
How does that happen and how do I fix it?
Looks like my comment worked as an answer, hence this answer.
What does $mysqli->$_POST['term'] do? I think you should have $text = $_POST['term'];. This should work.
Change the PHP to
$text = $_POST['term'];
$query = "SELECT name FROM males WHERE name LIKE '%" . $mysqli->real_escape_string($text) . "%' ORDER BY name ASC";
$result = $mysqli->query($query);
echo json_encode($result->fetch_all(MYSQLI_ASSOC));
You forgot to escape the input to prevent SQL injections. Additionally, use @mu's answer to fix your front-end code.
Your source callback isn't doing what you think it is. The current contents of the autocompleter are in request.term, not in $("#autocomplete").val(). You're sending an empty value for term back to your PHP and then your SQL looks like:
SELECT name FROM males WHERE name LIKE '%%' ORDER BY name ASC
which of courses matches everything in your males table. Your source callback should look like this:
source: function(request, response) {
$.ajax({
url: "http://localhost/testing/auto/search.php",
data: { term: request.term },
dataType: "json",
type: "POST",
success: function(data) {
response(data);
}
});
}
The jQuery-UI widget probably manipulates the DOM a fair bit so the #autocomplete input element may not have anything useful in it until the autocompleter has a final value for it.
For reference, here's what the fine manual has to say:
The third variation, the callback, provides the most flexibility, and can be used to connect any data source to Autocomplete. The callback gets two arguments:
- A
requestobject, with a single property calledterm, which refers to the value currently in the text input. For example, when the user entered "new yo" in a city field, the Autocomplete term will equal "new yo".
First make sure that your search.php is working. Switch to $_GET['term'] and run the page directly until you get the data you wanted.
I would personally change the query from from LIKE '%text%' to LIKE 'text%'.
After that use Firebug to examine the parameters that are transferred to the search.php with an AJAX call. BTW, are you sure that your jquery code is correct?
I think is your sql query. U were using
$query = "SELECT name FROM males WHERE name LIKE '%$text%' ORDER BY name ASC"
if your name list like
Aaron Alexander Maartha ...
If you type 'AA', the above query will result : Aaron, Maartha. Because the query will search matching given string between the whole strings. % text % means the query result will ignore the left and right result.
if you're looking for names field data you supposed to use query
$query = "SELECT name FROM males WHERE name LIKE '$text%' ORDER BY name ASC"
If you type 'AA, it will result : Aaron.
Note that jQuery only returns what you're asking to the database.
Use $_REQUEST['term'] instead $_POST['term'].
加载中,请稍侯......
精彩评论