How can GROUP BY be effectively used in PHP to group results from multiple tables?

When using GROUP BY in PHP to group results from multiple tables, you can achieve this by using SQL queries that join the tables together and then apply the GROUP BY clause to the desired column(s). This allows you to aggregate data from multiple tables based on a common column, such as an ID or category. 

<?php

// Connect 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 to select data from multiple tables and group by a column
$sql = "SELECT t1.column1, t2.column2, SUM(t1.amount) AS total_amount
        FROM table1 t1
        JOIN table2 t2 ON t1.id = t2.id
        GROUP BY t1.column1";

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

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Column1: " . $row["column1"]. " - Column2: " . $row["column2"]. " - Total Amount: " . $row["total_amount"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();

?>

Keywords

PHP GROUP BY multiple tables SQL database

Related Questions