Autocomplete Search Using PHP, PDO - MySQL & Ajax

Autocomplete Search Using PHP, PDO – MySQL & Ajax

—

by

Webbiz.ID
in

Creating an autocomplete search feature using PHP, PDO (MySQL), and Bootstrap with AJAX involves the following steps:

1. Database Setup

Assume you have a database table items with the following structure:

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Insert sample data
INSERT INTO items (name) VALUES
('Apple'),
('Banana'),
('Cherry'),
('Date'),
('Grape'),
('Mango'),
('Orange'),
('Peach'),
('Pear'),
('Watermelon');
PHP

2. Backend (PHP – Fetch Data)

Create a PHP script (search.php) that handles the AJAX request and fetches data from the database.

search.php:

<?php
if (isset($_GET['query'])) {
    $query = $_GET['query'];

    // Database connection
    $host = 'localhost'; // Update with your database host
    $db = 'your_database_name'; // Update with your database name
    $user = 'your_username'; // Update with your database username
    $pass = 'your_password'; // Update with your database password

    try {
        $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Fetch matching results
        $stmt = $pdo->prepare("SELECT name FROM items WHERE name LIKE :name LIMIT 10");
        $stmt->execute(['name' => '%' . $query . '%']);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Return JSON response
        echo json_encode($results);
    } catch (PDOException $e) {
        echo json_encode(['error' => $e->getMessage()]);
    }
}
?>
PHP

3. Frontend (HTML, Bootstrap, and JavaScript)

Create a simple HTML page with a search input box and a dropdown for autocomplete suggestions.

index.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Autocomplete Search</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h1>Autocomplete Search</h1>
        <div class="form-group position-relative">
            <input type="text" id="searchBox" class="form-control" placeholder="Search for items...">
            <ul id="suggestions" class="list-group position-absolute w-100" style="z-index: 1000;"></ul>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#searchBox').on('input', function () {
                const query = $(this).val();
                if (query.length > 0) {
                    $.ajax({
                        url: 'search.php',
                        type: 'GET',
                        data: { query: query },
                        success: function (data) {
                            const results = JSON.parse(data);
                            let suggestions = '';
                            if (results.length > 0) {
                                results.forEach(item => {
                                    suggestions += `<li class="list-group-item">${item.name}</li>`;
                                });
                            } else {
                                suggestions = '<li class="list-group-item text-muted">No results found</li>';
                            }
                            $('#suggestions').html(suggestions).show();
                        },
                        error: function (err) {
                            console.log('Error:', err);
                        }
                    });
                } else {
                    $('#suggestions').hide();
                }
            });

            // Hide suggestions when clicking outside
            $(document).on('click', function (e) {
                if (!$(e.target).closest('#searchBox, #suggestions').length) {
                    $('#suggestions').hide();
                }
            });

            // Populate search box on suggestion click
            $(document).on('click', '#suggestions li', function () {
                $('#searchBox').val($(this).text());
                $('#suggestions').hide();
            });
        });
    </script>
</body>
</html>
PHP

4. How It Works

  1. User Input:
    • The user types into the search box.
    • The input event triggers an AJAX request to search.php.
  2. AJAX Request:
    • The query parameter is sent to the server-side script.
    • The server fetches matching items from the items table.
  3. Response Handling:
    • The server returns a JSON array of results.
    • The frontend dynamically updates the suggestions list.
  4. Interactivity:
    • Clicking a suggestion populates the search box with the selected value.
    • Clicking outside the input or suggestion list hides the dropdown.

5. Features

  • Live Suggestions: As the user types, relevant results are fetched in real-time.
  • Debouncing (Optional): Use a debounce function to limit the frequency of AJAX calls for improved performance.
  • Styling: Bootstrap’s list-group and form-control classes provide a clean, responsive design.

6. Enhancements

  • Highlight Matching Text: Add bold or highlighted styling to the part of the suggestion that matches the query.
suggestions += `<li class="list-group-item"><strong>${item.name.substr(0, query.length)}</strong>${item.name.substr(query.length)}</li>`;
PHP
  • Pagination: Add pagination support to handle large datasets efficiently.
  • Error Handling: Display error messages if the server is unreachable or returns an error.

This setup is scalable and can be integrated with other frameworks if needed. Let me know if you’d like further optimizations or enhancements!

Leave a Reply

Your email address will not be published. Required fields are marked *

Artikel Terbaru ⭐