How to Create Search Functionality in PHP and MySQL

1. What Is Search Functionality in PHP?

Search functionality allows users to enter a query in a search box and retrieve matching data from a database. PHP communicates with MySQL to fetch the relevant records using SQL statements.

2. Why Implement a Search Feature?

Improves User Experience: Users can quickly find what they're looking for.
Boosts Engagement: Increases time on site by guiding users to relevant content.
Saves Time: No need to scroll or paginate manually.
SEO Advantage: Helps with internal linking and improves content discoverability.

3. Required Technologies

PHP (7.4 or 8.x)
MySQL or MariaDB
Basic HTML/CSS
(Optional) AJAX with JavaScript for live search

4. Setting Up the MySQL Database

Here's a simple posts table to use:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

Insert some sample data:

INSERT INTO posts (title, content) VALUES 
('PHP Search Tutorial', 'Learn how to create search functionality.'),
('MySQL Basics', 'Understand database operations.'),
('AJAX Live Search', 'Create dynamic search filters with AJAX.');

5. Creating a Basic Search Bar in PHP

HTML Search Form

<form method="GET" action="">
    <input type="text" name="query" placeholder="Search posts...">
    <button type="submit">Search</button>
</form>

6. Handling Search Requests with SQL

PHP Code

<?php
$conn = new mysqli("localhost", "root", "", "demo_db");

$searchTerm = isset($_GET['query']) ? $conn->real_escape_string($_GET['query']) : '';

$sql = "SELECT * FROM posts WHERE title LIKE '%$searchTerm%' OR content LIKE '%$searchTerm%'";
$result = $conn->query($sql);
?>

7. Displaying Results Dynamically

<?php
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<h2>" . htmlspecialchars($row['title']) . "</h2>";
        echo "<p>" . substr(htmlspecialchars($row['content']), 0, 150) . "...</p><hr>";
    }
} else {
    echo "<p>No results found for '<strong>" . htmlspecialchars($searchTerm) . "</strong>'.</p>";
}
?>

8. Preventing SQL Injection in Search

Use prepared statements for secure queries:

$stmt = $conn->prepare("SELECT * FROM posts WHERE title LIKE ? OR content LIKE ?");
$searchTerm = "%$searchTerm%";
$stmt->bind_param("ss", $searchTerm, $searchTerm);
$stmt->execute();
$result = $stmt->get_result();

This ensures your search box is protected from SQL injection attacks.

9. Adding Live Search with AJAX (Bonus)

HTML

<input type="text" id="searchBox" placeholder="Live search...">
<div id="results"></div>

JavaScript (AJAX)

document.getElementById("searchBox").addEventListener("keyup", function () {
    let query = this.value;

    fetch("live-search.php?query=" + query)
        .then(response => response.text())
        .then(data => {
            document.getElementById("results").innerHTML = data;
        });
});

PHP (live-search.php)

<?php
$conn = new mysqli("localhost", "root", "", "demo_db");
$searchTerm = isset($_GET['query']) ? $conn->real_escape_string($_GET['query']) : '';

$sql = "SELECT * FROM posts WHERE title LIKE '%$searchTerm%' LIMIT 5";
$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
    echo "<p>" . htmlspecialchars($row['title']) . "</p>";
}
?>

10. Using Full-Text Search in MySQL

Full-text search is faster and more accurate than LIKE.

Step 1: Add FULLTEXT index

ALTER TABLE posts ADD FULLTEXT(title, content);

Step 2: Update Query

$sql = "SELECT * FROM posts WHERE MATCH(title, content) AGAINST (? IN NATURAL LANGUAGE MODE)";

Use prepared statements for security.