How can sub-selects be used to handle complex calculations in MySQL queries in PHP?

Sub-selects can be used in MySQL queries to handle complex calculations by allowing you to perform calculations on the results of another query. This can be useful when you need to perform calculations on aggregated data or when you need to use the result of one query as input for another. In PHP, you can execute these complex queries using the mysqli or PDO extension to interact with the MySQL database. 

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

// Query with a sub-select to calculate the average price of products
$query = "SELECT category, AVG(price) AS avg_price FROM products GROUP BY category";

// Execute the query
$result = $connection->query($query);

// Loop through the results and output the average prices
while ($row = $result->fetch_assoc()) {
    echo "Category: " . $row['category'] . " - Average Price: " . $row['avg_price'] . "<br>";
}

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

Keywords

sub-selects complex calculations MySQL queries PHP database operations

Related Questions