How can JOIN operations be effectively used in PHP to retrieve data from multiple related tables?

When retrieving data from multiple related tables in PHP, JOIN operations can be effectively used to combine data from different tables based on a related column. By using JOIN operations, you can fetch data from multiple tables in a single query, reducing the number of database queries needed and improving performance. 

<?php
// Establish a database connection
$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 using JOIN to retrieve data from multiple related tables
$sql = "SELECT users.username, orders.order_id, orders.total_amount FROM users
        JOIN orders ON users.user_id = orders.user_id";

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

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Username: " . $row["username"]. " - Order ID: " . $row["order_id"]. " - Total Amount: " . $row["total_amount"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

PHP JOIN operations data retrieval related tables SQL.

Related Questions