How can PHP be used to efficiently handle complex queries involving multiple tables and calculations?

To efficiently handle complex queries involving multiple tables and calculations in PHP, you can use SQL JOINs to combine data from different tables, perform calculations within the query using SQL functions, and use PHP to process and display the results. 

<?php
// Connect to the database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Query to retrieve data from multiple tables and perform calculations
$query = "SELECT orders.id, orders.total_price, SUM(order_items.quantity * products.price) AS total_cost
          FROM orders
          JOIN order_items ON orders.id = order_items.order_id
          JOIN products ON order_items.product_id = products.id
          GROUP BY orders.id";

$result = mysqli_query($connection, $query);

// Process and display the results
while ($row = mysqli_fetch_assoc($result)) {
    echo "Order ID: " . $row['id'] . "<br>";
    echo "Total Price: $" . $row['total_price'] . "<br>";
    echo "Total Cost: $" . $row['total_cost'] . "<br>";
    echo "<br>";
}

// Close the connection
mysqli_close($connection);
?>

Keywords

SQL joins subqueries aggregate functions PHP PDO query optimization

Related Questions