How can the use of GROUP BY in a MySQL query affect the output of data related to members and their group affiliations?

When using GROUP BY in a MySQL query, the data will be grouped based on the specified column(s). This can affect the output of data related to members and their group affiliations because it will aggregate the data and only show one row per group. To solve this issue and get a list of all members and their group affiliations, you can use a JOIN statement to combine the tables and then use GROUP_CONCAT to concatenate the group affiliations for each member.

$query = "SELECT members.name, GROUP_CONCAT(groups.name) AS group_affiliations 
          FROM members 
          LEFT JOIN members_groups ON members.id = members_groups.member_id 
          LEFT JOIN groups ON members_groups.group_id = groups.id 
          GROUP BY members.id";

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

while($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . " belongs to groups: " . $row['group_affiliations'] . "<br>";
}