hominid4 hominid4 - 1 year ago 43
PHP Question

How to use 'dynamic' variable from multidimensional array elsewhere?

I have a mysql 'client-emails' table that contains a Client ID and email addresses that are assigned to those, each client can have several assigned emails; and the client-emails table is continually being updated and added to, so I don't have a set list of client_ids.

client_id | email
27 | email1@client27domain
27 | email2@client27domain
42 | email1@client42domain
42 | email2@client42domain
42 | email3@client42domain
46 | email1@client46domain

I have a email PHP script that sends out an email to these clients every night using a cron job. That works fine, but now we're needing to personalize these emails per client_id, so I'm needing to split each client group of emails per client, and when the email sends it knows which group of comma seperated emails to have in the "To" field per client_id, and a variable within the email body that pulls the Client ID.

To: $clientEmails
and within the message there'll be a "Your ID is: $clientId".

To: email1@client27domain,email2@client27domain
And in the message will be:
Your ID is: 27

Not sure if this is a correct start but I've been playing with a GROUP_CONCAT within the below query:

$sql = "SELECT client_id, GROUP_CONCAT(email) AS emails FROM portal_client_report_emails GROUP BY client_id ORDER BY client_id ASC";

And doing:

$grouped = array();
foreach ($rows as $item) {
$grouped[$item['client_id']][] = $item;

Dumps out:

array (
27 =>
array (
0 =>
array (
'client_id' => '27',
'emails' => 'email1@client27domain,email2@client27domain',
42 =>
array (
0 =>
array (
'client_id' => '42',
'emails' => 'email1@client42domain,email2@client42domain,email3@client42domain',
46 =>
array (
0 =>
array (
'client_id' => '46',
'emails' => 'email1@client46domain',

Am I able to loop through that array to tell my $to variable within my mail() to send a seperate email for each client_id to each of their imploded emails?

For my mail testing, I'm currently just using a basic mail() function:

$to = (needs to emails per respective client ID);
$subject = "Client Details";
$message = "Your Client ID: " . $clientID;

Could I please get help with the $to variable to do what I'm needing? Thanks!

Update: the below is only grabbing the first email address per client_id.

"SELECT client_id,email...";
$data = array();
while($row = mysqli_fetch_array($rows)){
$data[$row['client_id']][] = $row['email'];

Answer Source

Don't use group_concat. That takes multiple records and collapses it into a SINGLE value. It also has a length limit on the resulting string, and WILL silently truncate anything that exceeds the limit, leaving you with missing data and a possibly corrupted "final" one.

Just use a regular query:

SELECT id, email ...

then use a loop:

$data = array();
while($row = ... fetch ...) {
    $data[$row['id']][] = $row['email'];

That'll give you a nice 2D array of ids and email addresses, without having to UNDO any concatting or whatever - you just start using the addresses as is, no further processing needed:

foreach($data as $id => $addresses) {
    foreach($addresses as $addr) {
          send("Hi, $addr, your id is $id");