How can joins be effectively used in a single SQL query to retrieve and display all relevant information from different database tables in PHP?

To retrieve and display all relevant information from different database tables in PHP using joins, you can write a single SQL query that combines the necessary tables based on their relationships. By using JOIN clauses, you can specify how the tables are related and retrieve the desired data in a single query. 

<?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 with joins to retrieve relevant information
$sql = "SELECT orders.order_id, customers.customer_name, products.product_name
        FROM orders
        JOIN customers ON orders.customer_id = customers.customer_id
        JOIN products ON orders.product_id = products.product_id";

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

// Display the retrieved information
if ($result->num_rows > 0) {
    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";
}

$conn->close();
?>

Keywords

SQL joins database tables PHP retrieve data display data

Related Questions