Autocomplete with a Database Source

This tutorial uses a database table as the Autocomplete source. A PHP endpoint searches matching records and returns JSON in the label/value format expected by jQuery UI.

Live demo: Autocomplete with database source →

Larger demo: location and PIN-code search →

Input Field Top ↑

<input type="text" id="t1" autocomplete="off">

Request Suggestions from PHP Top ↑

$( "#t1" ).autocomplete({
    minLength: 1,

    source: function ( request, response ) {
        $.getJSON(
            "autocomplete-demo1-dtl.php",
            { term: request.term }
        )
        .done( response )
        .fail( function () {
            response( [] );
        } );
    }
});

The backend receives the typed text as term.

$term = trim($_GET['term'] ?? '');

Return label/value JSON Top ↑

Each row should provide the label shown to the user and a stable record value/ID.

[
    {"label": "Alex John", "value": 6},
    {"label": "John Deo", "value": 1}
]

Keep the Label in the Input after Selection Top ↑

select: function ( event, ui ) {
    event.preventDefault();
    this.value = ui.item.label;
}

Multiple-Word Database Search Top ↑

The original tutorial allowed a query such as Alex John to match individual words. Keep that learner purpose, but build prepared LIKE conditions rather than inserting search text into SQL.

$words = preg_split(
    '/\s+/',
    $term,
    -1,
    PREG_SPLIT_NO_EMPTY
);

$conditions = [];
$params = [];

foreach ($words as $index => $word) {
    $placeholder = ':term' . $index;
    $conditions[] = "name LIKE $placeholder";
    $params[$placeholder] = '%' . $word . '%';
}

$sql = "
    SELECT name AS label, id AS value
    FROM student
    WHERE " . implode(' OR ', $conditions) . "
    ORDER BY name
    LIMIT 30
";

$stmt = $dbo->prepare($sql);
$stmt->execute($params);

echo json_encode(
    $stmt->fetchAll(PDO::FETCH_ASSOC)
);

Related reading: SQL LIKE, multi-keyword search, PDO records, PDO connection, and JSON output.

MySQLi Alternative Top ↑

If your project uses MySQLi instead of PDO, use prepared statements there as well. The older tutorial also linked to the MySQLi section.

Load Full Details after Selection Top ↑

select: function ( event, ui ) {
    event.preventDefault();
    this.value = ui.item.label;

    $( "#d1" ).load(
        "autocomplete-demo1-dtl-db.php",
        { id: ui.item.value }
    );
}

This preserves the original jQuery load() workflow without constructing an unencoded query string manually.

Demo: load selected database record details →

Detail Endpoint with a Prepared Query Top ↑

$id = filter_input(
    INPUT_GET,
    'id',
    FILTER_VALIDATE_INT
);

$stmt = $dbo->prepare(
    'SELECT name, class, mark
     FROM student
     WHERE id = :id'
);

$stmt->execute(['id' => $id]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);

Files Used by These Demos Top ↑

Download the existing Autocomplete database example ZIP

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: