Autocomplete Textbox results based from SQL database
I'm trying to create an auto-complete function into a textbox but the result should come from my SQL database.
Here's the code that i'm trying to configure:
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') ?>;
Variable availableTags is 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.
Here's my autocomplete.php file:
<?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
$name=mysqli_real_escape_string($con,$row['name']);
$json[]=$name;
How can I insert the fetched words from autocomplete.php into availableTags variable?
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.
Answer
The jQuery UI autocomplete can take 3 different types of values of the source option:
- An array containing the list of things to fill in the auto complete with
- A string containing 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 function that retrieves the data and then calls a callback with that data.
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.
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 addSlashes to 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.
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 statement to ensure the user provided data in $term doesn't open you up to SQL injection:
<?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.
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_string before 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.
If you really want to stick with the non-prepared statement, the code would look something like this:
<?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);
?>