JQueryUI Autocomplete results not showing, but coming up in DevTools

Question

I'm working on a basic search box for a large list of people on our MySQL server using JQueryUI Autocomplete and PHP, though when I type the actual prompt, the results dropdown won't show.

I've checked in DevTools and the data is coming across just fine based on my term.

Search Term

DevTools Results Here

$(function() {
    $("#search").autocomplete({
        source: function(request, response) {
            $.ajax({
                type: "GET",
                url: "search.php",
                data: {
                    term: $("#search").val()
                },
                dataType: "json",
                success: function(data) {
                    response($.map(data, function(item) {
                        return item.Name;
                    }));
                }
            });
        }
    });
});

I'm not sure if this is an issue with the plugin itself, given it is a little dated, but I'm stumped as to why none of my results will show.

<?php
    require_once 'db.php';

    $search = $_GET['term'];

    $search = "%".$search."%";

    $searchAlumni = $pdo->prepare("SELECT CONCAT(Last,', ', First) AS Name FROM Constituents WHERE Last LIKE :last");
    $searchAlumni->bindParam(':last', $search);
    //$searchAlumni->bindParam(':first', $search);
    $searchAlumni->execute();

    while($result = $searchAlumni->fetch()) {
        echo json_encode($result);
    }

?>

Any help or suggestions would be greatly appreciated.

Answer 1

The primary issue may be in your PHP. You're gathering results and then echoing them line by line when you want to build an array and then echo the array value. Also there are some things you can improve on your JavaScript.

JavaScript

$(function() {
  $("#search").autocomplete({
    minLength: 3,
    source: function(request, response) {
      $.getJSON("search.php", { term: request.term }, function(data) {
        var results = [];
        $.each(data, function(i, name){
          results.push(name);
        });
        response(results);
      });
    }
  });
});

This isn't much different than your code. Using $.getJSON() is just shorthand for what you had written in $.ajax(). The benefit here is you'll always get a results array, even if empty. So it will always trigger a response.

As you mentioned you would get a "large list" of names, I advise using minLength option.

The minimum number of characters a user must type before a search is performed. Zero is useful for local data with just a few items, but a higher value should be used when a single character search could match a few thousand items.

So if you want it to start responding sooner, consider 2. For example, if the user enter "jo", your results might include: "jon", "john", "joanna", "bojon" etc... Using 3, the User would enter "joh", and still get "john" yet it would be quick and a smaller result set.

PHP

<?php
require_once 'db.php';
$search = $_GET['term'];
$searchAlumni = $pdo->prepare("SELECT CONCAT(Last,', ', First) AS Name FROM Constituents WHERE Last LIKE :last");
$searchAlumni->bindParam(':last', "%$search%");
$searchAlumni->execute();
$results = array();
while($result = $searchAlumni->fetch(PDO::FETCH_OBJ)) {
  array_push($results, $result->Name);
}
header('Content-Type: application/json');
echo json_encode($results);
?>

Again not drastically different. You can see in the while statement we're populating the array with just the resulting Name data from the fetched Object. This should build an array like:

[
  "Smith, John",
  "Smith, Jane"
]

This complete array is then echo'd out via JSON. The header detail helps the browser know what type of data type is being sent back. It's just good form these days as jQuery does a lot to anticipate.

You should also consider what to do if there is a Query Error and how to handle that in PHP, what to send back to JavaScript, and how to handle that in JS too.