How to Create Search in PHP with MySQL: Simple Guide

To create a search in PHP with MySQL, use an HTML form to get the search term, then run a SELECT query with a LIKE clause in PHP to find matching records. Use prepared statements to safely pass the search term and display the results.

Syntax

The basic syntax involves using a SQL SELECT statement with a WHERE clause and the LIKE operator to find matching text. In PHP, you prepare and execute this query with the search term.

• SELECT * FROM table WHERE column LIKE '%search_term%': Finds rows where column contains search_term.
• Use prepare() and bind_param() in PHP to safely insert the search term.
• Fetch and display results after executing the query.

php

<?php
// Prepare SQL with LIKE and placeholder
$sql = "SELECT * FROM products WHERE name LIKE ?";

// Prepare statement
$stmt = $conn->prepare($sql);

// Add wildcards to search term
$searchTerm = "%" . $userInput . "%";

// Bind parameter
$stmt->bind_param("s", $searchTerm);

// Execute
$stmt->execute();

// Get results
$result = $stmt->get_result();
?>

Example

This example shows a simple search form and PHP code that connects to MySQL, runs a search query, and displays matching product names.

php

<?php
// Connect to MySQL
$conn = new mysqli('localhost', 'root', '', 'shop');
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// Check if search submitted and not empty
if (isset($_GET['search']) && trim($_GET['search']) !== '') {
    $searchTerm = $_GET['search'];

    // Prepare SQL with LIKE
    $sql = "SELECT * FROM products WHERE name LIKE ?";
    $stmt = $conn->prepare($sql);
    $likeTerm = "%" . $searchTerm . "%";
    $stmt->bind_param('s', $likeTerm);
    $stmt->execute();
    $result = $stmt->get_result();

    echo "<h2>Search results for '" . htmlspecialchars($searchTerm) . "':</h2>";
    if ($result->num_rows > 0) {
        echo "<ul>";
        while ($row = $result->fetch_assoc()) {
            echo "<li>" . htmlspecialchars($row['name']) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No results found.</p>";
    }
}
?>

<form method="get">
    <input type="text" name="search" placeholder="Search products..." required>
    <button type="submit">Search</button>
</form>

Output

<h2>Search results for 'apple':</h2><ul><li>Apple iPhone</li><li>Apple MacBook</li></ul>

Common Pitfalls

Common mistakes when creating search in PHP with MySQL include:

• Not using prepared statements, which risks SQL injection attacks.
• Forgetting to add wildcards (%) around the search term for partial matches.
• Not sanitizing user input before displaying results, which can cause security issues.
• Not checking if the search term is empty before querying.

php

<?php
// Wrong way (vulnerable to SQL injection):
$searchTerm = $_GET['search'];
$sql = "SELECT * FROM products WHERE name LIKE '%" . $searchTerm . "%'";
$result = $conn->query($sql);

// Right way (safe with prepared statements):
$searchTerm = $_GET['search'];
$sql = "SELECT * FROM products WHERE name LIKE ?";
$stmt = $conn->prepare($sql);
$likeTerm = "%" . $searchTerm . "%";
$stmt->bind_param('s', $likeTerm);
$stmt->execute();
$result = $stmt->get_result();
?>

Quick Reference

• Use LIKE '%term%' in SQL to search for partial matches.
• Always use prepared statements with bind_param() to avoid SQL injection.
• Sanitize output with htmlspecialchars() to prevent XSS attacks.
• Check if search input is set and not empty before querying.

Key Takeaways

Use SQL LIKE with wildcards (%) to find partial matches in MySQL.

Always use prepared statements in PHP to safely include user input in queries.

Sanitize user input and output to keep your app secure.

Check that the search term is provided before running the query.

Display results clearly and handle the case when no matches are found.