What is the best way to retrieve data from multiple tables in PHP?

When retrieving data from multiple tables in PHP, the best way is to use SQL JOIN statements to combine the data from different tables based on a common column. By using JOINs, you can fetch related data from multiple tables in a single query, reducing the number of queries and improving performance. 

<?php
// Establish a connection to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);

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

// SQL query to retrieve data from multiple tables using JOIN
$sql = "SELECT orders.order_id, customers.customer_name, products.product_name
        FROM orders
        INNER JOIN customers ON orders.customer_id = customers.customer_id
        INNER JOIN products ON orders.product_id = products.product_id";

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

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Order ID: " . $row["order_id"]. " - Customer Name: " . $row["customer_name"]. " - Product Name: " . $row["product_name"]. "<br>";
    }
} else {
    echo "0 results";
}

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

Keywords

SQL JOIN PHP PDO Data retrieval Multiple tables Database integration

Related Questions