Autocomplete With PHP Get ID By Name First And Last

Mr. Loba Loba · · 11 min read

Table Of Content

  1. Understanding the Goal
  2. Setting Up the Frontend (HTML, CSS, jQuery)
  3. Setting Up the Backend (PHP)
  4. Database Setup
  5. Testing the Autocomplete
  6. Common Issues and Solutions
  7. Enhancements and Further Steps
  8. Conclusion

Hey guys! Ever wondered how to implement a super cool autocomplete feature where you not only get name suggestions but also snag the associated ID? This is incredibly useful when you want to link the selected name to other data in your database. In this guide, we'll break down how to achieve this using PHP, jQuery, and Ajax. We'll start with the basic setup and then dive deep into the code, making sure you understand each step. So, let's get started!

Understanding the Goal

Our primary goal is to create an autocomplete functionality that, as the user types a name, suggests names from the database and, upon selection, returns the corresponding ID. This involves a few key components:

  1. Frontend (HTML, CSS, jQuery): The user interface where the user types, suggestions appear, and selections are made. We'll use jQuery for handling events and Ajax requests.
  2. Backend (PHP): The server-side script that queries the database based on the user's input and returns the results.
  3. Database: A database containing the names and IDs. For this example, let’s assume we have a table named users with columns id, first_name, and last_name.

Setting Up the Frontend (HTML, CSS, jQuery)

First, let’s set up the HTML structure. We need an input field for the user to type in, a container to display the autocomplete suggestions, and a place to show the selected ID. Here’s the basic HTML structure:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Autocomplete with ID</title>
    <link rel="stylesheet" href="css/style.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</head>
<body>
    <div class="container">
        <h1>Autocomplete with ID</h1>
        <div class="autocomplete">
            <input type="text" id="name" name="name" placeholder="Enter name">
            <ul class="autocomplete-list"></ul>
        </div>
        <div class="selected-id">
            <strong>Selected ID:</strong> <span id="selected-id"></span>
        </div>
    </div>
    <script src="js/script.js"></script>
</body>
</html>

Next, let’s add some CSS to style the page and the autocomplete suggestions. Create a file named style.css inside the css folder and add the following:

body {
    font-family: Arial, sans-serif;
    background-color: #f4f4f4;
    margin: 0;
    padding: 0;
    display: flex;
    justify-content: center;
    align-items: center;
    min-height: 100vh;
}

.container {
    background-color: #fff;
    padding: 20px;
    border-radius: 8px;
    box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
    width: 80%;
    max-width: 600px;
}

.autocomplete {
    position: relative;
}

.autocomplete input {
    width: 100%;
    padding: 10px;
    border: 1px solid #ccc;
    border-radius: 4px;
    box-sizing: border-box;
    font-size: 16px;
}

.autocomplete-list {
    list-style: none;
    padding: 0;
    margin: 0;
    position: absolute;
    top: 100%;
    left: 0;
    width: 100%;
    border: 1px solid #ccc;
    border-top: none;
    border-radius: 0 0 4px 4px;
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
    background-color: #fff;
    z-index: 10;
    display: none;
}

.autocomplete-list li {
    padding: 10px;
    cursor: pointer;
}

.autocomplete-list li:hover {
    background-color: #f0f0f0;
}

.selected-id {
    margin-top: 20px;
    font-size: 18px;
}

Now, let’s add the jQuery part to handle the autocomplete functionality. Create a file named script.js inside the js folder and add the following:

$(document).ready(function() {
    $("#name").on("input", function() {
        var query = $(this).val();
        if (query.length > 2) { // Minimum 3 characters to trigger autocomplete
            $.ajax({
                url: "autocomplete.php",
                method: "POST",
                data: {query: query},
                dataType: "json",
                success: function(data) {
                    var list = $(".autocomplete-list");
                    list.empty();
                    if (data.length > 0) {
                        list.show();
                        $.each(data, function(index, item) {
                            list.append("<li data-id='" + item.id + "'>" + item.first_name + " " + item.last_name + "</li>");
                        });
                    } else {
                        list.hide();
                    }
                }
            });
        } else {
            $(".autocomplete-list").hide();
            $("#selected-id").text("");
        }
    });

    $(".autocomplete-list").on(
        "click",
        "li",
        function() {
            $("#name").val($(this).text());
            $(".autocomplete-list").hide();
            $("#selected-id").text($(this).data("id"));
        }
    );

    $(document).on("click", function(event) {
        if (!$(event.target).closest(".autocomplete").length) {
            $(".autocomplete-list").hide();
        }
    });
});

This jQuery script does the following:

  • Listens for input in the #name field.
  • If the input length is greater than 2, it sends an Ajax request to autocomplete.php.
  • On success, it populates the .autocomplete-list with the suggestions.
  • If a suggestion is clicked, it fills the #name field and displays the selected ID.
  • It also handles clicks outside the autocomplete box to hide the suggestions.

Setting Up the Backend (PHP)

Now, let’s create the backend PHP script that queries the database and returns the results. Create a file named autocomplete.php in the root directory and add the following:

<?php
// Database configuration
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($host, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get the search query
$query = $_POST['query'];

// Prepare the SQL statement
$sql = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '%" . $query . "%' OR last_name LIKE '%" . $query . "%' LIMIT 10";

// Execute the query
$result = $conn->query($sql);

// Prepare the results array
$data = array();

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

// Return the results as JSON
echo json_encode($data);

// Close the connection
$conn->close();
?>

Remember to replace placeholders for database credentials (your_username, your_password, your_database) with your actual database information. This PHP script does the following:

  • Connects to the MySQL database.
  • Retrieves the search query from the POST request.
  • Constructs an SQL query to search for names in the users table.
  • Executes the query and fetches the results.
  • Returns the results as a JSON array.

Database Setup

Before running the code, ensure you have a database named your_database and a table named users. Here’s an example SQL script to create the table and insert some data:

CREATE DATABASE IF NOT EXISTS your_database;

USE your_database;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);

INSERT INTO users (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Peter', 'Jones'),
('Alice', 'Brown'),
('Bob', 'Davis');

Testing the Autocomplete

Now that we have the frontend, backend, and database set up, it’s time to test the autocomplete functionality. Open the index.html file in your browser. As you type in the input field, you should see suggestions appearing. When you select a suggestion, the corresponding ID should be displayed below the input field.

Common Issues and Solutions

  1. No suggestions appearing:

    • Check the browser console for JavaScript errors.
    • Ensure the autocomplete.php file is returning a valid JSON response.
    • Verify that the database connection details are correct.
    • Make sure the input field has focus and you’ve typed at least 3 characters (as per our script).

  2. ID not being displayed:

    • Check if the data-id attribute is correctly set on the list items.
    • Verify that the jQuery selector #selected-id is correct.
    • Inspect the HTML to ensure the span element with the ID selected-id exists.

  3. Database connection errors:

    • Double-check the database credentials (username, password, database name).
    • Ensure the MySQL server is running.
    • Verify that the PHP script has the necessary permissions to access the database.

Enhancements and Further Steps

  1. Styling: You can further enhance the styling of the autocomplete suggestions using CSS to match your website’s design.
  2. Performance: For large datasets, consider implementing server-side caching or using a more efficient database query.
  3. Security: Always sanitize user input to prevent SQL injection attacks. Use prepared statements or parameterized queries.
  4. Accessibility: Ensure the autocomplete functionality is accessible by using ARIA attributes and proper keyboard navigation.

Conclusion

And there you have it! You've successfully implemented an autocomplete feature that returns IDs based on the first and last names using PHP, jQuery, and Ajax. This functionality is a fantastic addition to any application where you need to link user input to specific data in your database. By following the steps outlined in this guide, you can create a seamless and efficient user experience. Remember to always prioritize security and performance as you continue to develop your application. Happy coding, guys!

Related Posts