How can GROUP BY be used in MySQL queries to group data from multiple tables in PHP?

When using GROUP BY in MySQL queries to group data from multiple tables in PHP, you need to ensure that the columns you select in the SELECT statement are either included in the GROUP BY clause or are aggregate functions. This is because MySQL requires all non-aggregated columns in the SELECT statement to be included in the GROUP BY clause. Failure to do so will result in an error. 

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

// Query to retrieve data from multiple tables and group by a specific column
$query = "SELECT table1.column1, SUM(table2.column2) 
          FROM table1 
          JOIN table2 ON table1.id = table2.id 
          GROUP BY table1.column1";

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

// Fetch and display the results
while ($row = $result->fetch_assoc()) {
    echo $row['column1'] . ": " . $row['SUM(table2.column2)'] . "<br>";
}

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

Related Questions