MySQL database table search script in PHP

Below is a simple PHP script that demonstrates how to search a MySQL database table using a user-provided search term. The script includes comments explaining each part.

Prerequisites

Make sure you have:

A MySQL database set up.
A table in the database you want to search (for this example, we’ll use a table called products with columns id, name, and description).
PHP installed on your server.

PHP Script: search.php

<?php
// Step 1: Connect to the database
$servername = "localhost"; // Your database server
$username = "username"; // Your database username
$password = "password"; // Your database password
$dbname = "database_name"; // Your database name

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Step 2: Get the search term from the user (from a form, for example)
$searchTerm = isset($_GET['search']) ? $_GET['search'] : '';

// Step 3: Prepare a SQL statement to prevent SQL injection
$sql = "SELECT * FROM products WHERE name LIKE ? OR description LIKE ?";
$stmt = $conn->prepare($sql);

// Create the wildcard search pattern
$searchPattern = "%" . $searchTerm . "%";

// Bind parameters to the SQL query
$stmt->bind_param("ss", $searchPattern, $searchPattern);

// Step 4: Execute the statement
$stmt->execute();

// Step 5: Get the results
$result = $stmt->get_result();

// Step 6: Check if any results were found
if ($result->num_rows > 0) {
    // Step 7: Output the results
    echo "<h2>Search Results:</h2>";
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Description: " . $row["description"] . "<br>";
    }
} else {
    echo "No results found.";
}

// Step 8: Close the statement and connection
$stmt->close();
$conn->close();
?>

HTML Form for User Input: search_form.html

This is a simple HTML form to take user input for the search term.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Search Products</title>
</head>
<body>
    <h1>Search Products</h1>
    <form action="search.php" method="get">
        <input type="text" name="search" placeholder="Enter product name or description">
        <input type="submit" value="Search">
    </form>
</body>
</html>

Explanation:

Database Connection: The script starts by establishing a connection to the MySQL database using the mysqli object.
User Input: It retrieves the search term from the URL parameters (using $_GET).
Prepared Statements: A SQL query template is created to prevent SQL injection vulnerabilities. The LIKE keyword is used for partial matches, and placeholders (?) are used to bind parameters.
Execution: The prepared statement is executed.
Results: The results are processed. If any records match the search criteria, they are displayed; otherwise, a “No results found” message is shown.
Closing Resources: Finally, the script closes the prepared statement and the database connection.

You can run the HTML file in your browser, enter a search term, and it will display the matching results from the MySQL database when you submit the form.

Make sure to replace the placeholders in the connection part of the PHP script with your actual database credentials.