How to filter items in while loop?

Issue

SQL Table [orders]

| orderId | dueDate    | emailAddress   |
| ------- | ---------- | -------------- |
| 1010101 | 10/11/2021 | [email protected]  |
| 1010102 | 10/11/2021 | [email protected]  |
| 1010103 | 10/11/2021 | [email protected]  |
| 1010104 | 10/11/2021 | [email protected] |
| 1010105 | 10/11/2021 | [email protected] |
| 1010106 | 10/11/2021 | [email protected] |

PHP Script

$query = "SELECT * FROM orders";
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($result)) {
  $order = $row['orderId'];
  $to = $row['emailAddress'];
  $sub = "Payment Due Reminder";
  $body = "Due reminder message with order ID $order";
  mail($to, $sub, $body);
}

My Requirement

Now I want to send only one email listing the three order IDs rather than sending three emails to the same recipient. Is there a way to achieve it?

Solution

I would handle this within MySQL by using an aggregation query:

$query = "SELECT dueDate, emailAddress, GROUP_CONCAT(orderId) AS all_orders
FROM orders
GROUP BY dueDate, emailAddress";

$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($result)) {
    $all_orders = $row['all_orders'];
    $to = $row['emailAddress'];
    $sub = "Payment Due Reminder";
    $body = "Due reminder message with order ID $all_orders";
    mail($to, $sub, $body);
}

Answered By – Tim Biegeleisen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0