SQL Table [orders]
| orderId | dueDate | emailAddress |
| ------- | ---------- | -------------- |
| 1010101 | 10/11/2021 | joe@gmail.com |
| 1010102 | 10/11/2021 | joe@gmail.com |
| 1010103 | 10/11/2021 | joe@gmail.com |
| 1010104 | 10/11/2021 | john@gmail.com |
| 1010105 | 10/11/2021 | john@gmail.com |
| 1010106 | 10/11/2021 | john@gmail.com |
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? It would be very helpful if someone could help me with this!
Thanks!
CodePudding user response:
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);
}
CodePudding user response:
Why is there duplicate data in the table?
<?php
$query = "SELECT * FROM orders";
$result = mysqli_query($conn, $query);
$sentList = [];
while ($row = mysqli_fetch_assoc($result)) {
if (key_exists($row['emailAddress'], $sentList)) {
continue;
}
$order = $row['orderId'];
$to = $row['emailAddress'];
$sub = "Payment Due Reminder";
$body = "Due reminder message with order ID $order";
mail($to, $sub, $body);
$sentList[$to] = 1;
}