What are the best practices for structuring SQL statements in PHP to retrieve and display data from multiple tables?

When retrieving and displaying data from multiple tables in PHP using SQL statements, it is best to use JOIN clauses to combine the tables based on a common key. This allows you to fetch related data from different tables in a single query, reducing the number of queries executed and improving performance. Additionally, using aliases for table names and columns can make the SQL statement more readable and easier to maintain. 

<?php
// Establish a database connection
$connection = new mysqli('localhost', 'username', 'password', 'database_name');

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

// SQL query to retrieve data from multiple tables using JOIN
$sql = "SELECT t1.column1, t2.column2
        FROM table1 t1
        JOIN table2 t2 ON t1.common_key = t2.common_key";

$result = $connection->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Column1: " . $row["column1"]. " - Column2: " . $row["column2"]. "<br>";
    }
} else {
    echo "0 results";
}

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

Keywords

SQL statements PHP data retrieval multiple tables data display

Related Questions