What are the best practices for expanding a search function to include multiple tables in PHP?

When expanding a search function to include multiple tables in PHP, one approach is to use SQL JOIN statements to combine the tables and search for the desired data across them. By using JOINs, you can retrieve related data from multiple tables based on a common key or condition. Additionally, you can use WHERE clauses to filter the results based on specific search criteria. 

<?php
// Assuming $searchTerm is the search term entered by the user

// Establish a database connection
$pdo = new PDO("mysql:host=localhost;dbname=your_database", "username", "password");

// Prepare the SQL query with JOIN statements to search across multiple tables
$sql = "SELECT * FROM table1 
        JOIN table2 ON table1.common_key = table2.common_key
        WHERE table1.column_name LIKE :searchTerm OR table2.column_name LIKE :searchTerm";

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

// Bind the search term to the parameter
$stmt->bindParam(':searchTerm', $searchTerm, PDO::PARAM_STR);

// Execute the query
$stmt->execute();

// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Display the search results
foreach($results as $result) {
    echo $result['column_name'] . "<br>";
}
?>

Keywords

SQL joins PHP PDO search function multiple tables database normalization

Related Questions