JQuery UI autocomplete can take 3 different types of source values:
- an array containing a list of things to fill a car filled with
- A string containing the URL of a script that filters the list and sends us the results. The plugin will accept the text entered into it and send it as a
term parameter to the query string added to the specified URL. - Function which retrieves data and then calls a callback with that data.
The source code uses the first, an array.
var availableTags = [ "autocomplete.php"; ];
What autocomplete says is that the string "autocomplete.php" is the only thing on the list of things to autocomplete with.
I think you tried to insert it with something like this:
$(function() { var availableTags = [ <?php include("autocomplete.php"); ?>; ]; $( "#tags" ).autocomplete({ source: availableTags }); });
This is likely to work fine if you assume that the list of things returned from the database will always remain short. The implementation of this method is rather fragile, because since you just push the original output from PHP to your JS. If the returned data contains " , you may need to use addSlashes to avoid it. However, you must modify the query to return a single field, not * , maybe you only need one field as a label in the autocomplete of not the entire line.
The best approach, especially if the list could potentially become very large, is to use the second method:
$(function() { var availableTags = "autocomplete.php"; $( "#tags" ).autocomplete({ source: availableTags }); });
This will require changes to the back-end script that captures the list so that it performs filtering. This example uses a prepared statement to ensure that the data provided by the user in $term does not open you until the 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); ?>
Some time has passed since I worked with mysqli, so this code may require some configuration, since it has not been tested.
It would be nice to get used to using prepared statements, because if used correctly, they make SQL injection impossible. Instead, you can use the usual unprepared statement, avoiding every element provided by the user, mysqli_real_escape_string , before inserting it into the SQL statement. However , this is very error prone. Just forget about avoiding one thing in order to open yourself up with attacks. Most of the main “hacks" in recent history are related to sloppy coding, which represents SQL injection vulnerabilities.
If you really want to stick with an unprepared statement, the code will 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);