JQuery Autocomplete from Database

JQuery Autocomplete from Database

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Debugging Legacy Autocomplete Implementations

Building an autocomplete feature that queries a database sounds straightforward, but integrating jQuery UI's autocomplete widget often reveals subtle friction points between client-side expectations and server-side behavior. The core issue usually stems from how the JSON response is structured or how the underlying PHP version handles deprecated functions.

The provided example uses a classic setup: a form field named hint, a local PHP endpoint called gethint.php, and jQuery UI loaded from a CDN. While the logic appears sound at first glance, there are three critical failure vectors in this specific implementation.

The JSON Structure Mismatch

The most immediate error often lies in how jQuery UI parses the response. By default, the widget expects an object array where each item has a label (for display) and a value (for submission). The PHP code correctly maps these fields:

$json[]=array(
  'value' => $row['fname'],
  'label' => $row['fname']
);

However, if the database returns no results for a specific query string, the $json array is empty. While json_encode([]) produces an empty object {}, some older versions of jQuery UI might misinterpret an empty list or fail to clear previous suggestions cleanly if the session state isn't managed perfectly. More importantly, if the input field name in HTML (hint) does not match exactly what your backend logic expects (though here they align), the form submission will be incorrect once a user selects a suggestion.

Deprecated Database Functions and Security Risks

The gethint.php script relies on mysql_query(), which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0. If your server runs PHP 7 or higher, this script will throw a fatal error before generating any JSON output, causing the autocomplete to simply fail silently to the user.

Furthermore, using mysql_* functions exposes you to SQL injection vulnerabilities unless you are strictly sanitizing inputs (which $q currently is not). The code uses string concatenation ("%$q%") directly in the query. If a user types ' OR '1'='1, your database returns every row from the Property table, breaking the autocomplete logic and potentially leaking sensitive data.

To fix this, you must migrate to MySQLi or PDO. Here is how the PHP endpoint should look with modern best practices:

<?php
// Use PDO for security and version compatibility
$host = "localhost";
$user = "username";
$pass = "password";
$db   = "database_name";

$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$q = $_REQUEST['q'];
// Escape special characters to prevent SQL injection
$q = $conn->real_escape_string($q);

$sql = "SELECT fname FROM Property WHERE fname LIKE :q ORDER BY fname LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->execute([':q' => "%$q%"]);

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Ensure the output is a valid array of objects for jQuery UI
header('Content-Type: application/json');
echo json_encode($results);
?>

Limiting Results and Performance

A common pitfall in autocomplete implementations is not limiting the number of results. The original query SELECT fname FROM Property WHERE fname LIKE '%$q%' could theoretically return thousands of rows if the table is large. This floods the browser's memory and slows down the network request.

Always use a LIMIT clause, as shown in the PDO example above (LIMIT 10). This ensures the UI remains responsive even with massive datasets. Additionally, note that the query searches the entire column name (fname LIKE '%$q%'). For better performance on large tables, consider creating a database index on the search column or using full-text search indexes if exact prefix matching isn't required.

Modern Alternatives and Resources

While jQuery UI autocomplete is robust, maintaining legacy codebases requires constant patching of dependencies like jQuery 1.7.2. Modern frameworks often simplify this workflow. For instance, Laravel includes a powerful autocomplete helper that handles the JSON response formatting automatically, removing the need to manually encode arrays in your controller.

If you are building a new application or refactoring an existing one, checking resources like https://laravelcompany.com can provide insights into modernizing legacy PHP applications and adopting current best practices for AJAX interactions without reinventing the wheel.

In summary, ensure your PHP version is up to date, switch to prepared statements to secure your data, and always limit your SQL results to maintain performance.

Our network