Query Help Grouping

[stivenbr]

Hello,

 

I was wondering if maybe someone could help me on this. I am having trouble with this query. I need to send an email to the users but group all the records related to that user instead of sending one email per record to the user. I hope this makes sense. Here is the code I have so far. All records are being displayed but how can I get all the records for each user (infant_specialist field) send one email and then go to the next user and send the other email and so on.

 

Thank you for your help

$conn = mysqli_connect('localhost','root','','dbname');

$sql = "SELECT case_no, client_name, infant_specialist.full_name, infant_specialist.is_email FROM ies_clients INNER JOIN infant_specialist ON ies_clients.infant_specialist = infant_specialist.is_id WHERE (date_closed IS NULL OR date_closed >=  DATE_FORMAT(NOW() ,'%Y-%m-01')) ORDER BY client_name ASC";
$q = mysqli_query($conn,$sql);

//echo mysqli_num_rows($q) . '<br/>';

while($row = mysqli_fetch_array($q) ){
	
	echo $row['client_name'] . ' --- ' , $row['full_name'] . ' --- ' . $row['is_email'] . '<br/>';
}

Edited October 7, 2014 by stivenbr

[ginerjm]

Order the query by that field and then in your processing loop add a field to retain the last value of that field.  At the beginning of the loop, check if the new record has the same value, and if so accumulate your data in an 'output' var for the email.  Once the new record doesn't match, do your email, re-init the output var and then continue on and process the record that started the new value and keep on going.

 

Pseudo code:

 

lastvalue='xxx';

while(loop logic)

{

  if newvalue <> lastvalue and lastvalue <> 'xxx

    {

       call your output function that sends email with accumulated data in vars

    }

  process new record by taking new data and accumulating into vars

   lastvalue = newvalue

}

 

Hope this makes sense.

[Barand]

You will also need to call the same output function after the loop to process the final email

[stivenbr]

Thank you for your help, I am still having a little trouble I'm not sure what I am doing wrong... here is my code. 

$is = '';
$msg = '';
while($row = mysqli_fetch_array($q) ){
	
	if($is == $row['is_id']){	
		$msg .= $row['client_name'] . ' --- ' . $row['full_name'] . ' LINK <br/>';
	}
	
	if($is != $row['is_id']){
		echo $msg . ' email sent <br/>';
	}
	
	$is = $row['is_id'];
}

[Barand]

Like this

<?php 
    $is = '';
    $msg = '';
    while($row = mysqli_fetch_array($q) ){
        if($is != $row['is_id']){
            if ($is != '') {
                // send  $msg to $is email
            }
            $msg = '';
            $is = $row['is_id'];
        }
        $msg .= $row['client_name'] . ' --- ' . $row['full_name'] . ' LINK <br/>';
    }
    // send  final $msg to $is email
?>

[stivenbr]

thank you for your help.. this is working for me too I think it looks ok.. 

$is = '';
$msg = '';
while($row = mysqli_fetch_array($q) ){
	
	if($is == $row['is_id']){	
		$msg .= $row['client_name'] . ' - ' . $row['full_name'] . ' - ' . $row['month'] . '<br/>';
	}
	
	if($is != $row['is_id']){
		if(!empty($msg)){
			echo $msg . ' email sent <br/><br/><br/>';
		}
		$msg = '';
	}
	
	$is = $row['is_id'];
}

[stivenbr]

I have to add one more thing to this email :/ I need to set what template is needed and this is dependent on a date field from the record. this logic is driving me a little crazy I hope I can explain myself :/ 

 

I have report month let's say report month is 11 this is the check I need to do against DB Field here is a little example 

 

if month == 11 then 

for months 4 and 10 template = Quarterly 

for month 7 then template = Semi-Annual

for month 11 then Template = Annual. 

 

it depends on the report month 

something like this 

 

report month - 3 = quarterly and report month - 8 = quarterly, report month - 6 = semi-annual report month = 11 then its annual the code I have only works for this month but it won't work when the month lands on January through June

 

here is what I have so far 

$is = '';
$msg = '';
while($row = mysqli_fetch_array($q) ){
	
	if($is == $row['is_id']){
		
		if($row['month'] == $month){
			$template = 'Annual';
		}
		if($row['month'] == ($month - 6) ){
			$template = 'Semi-Annual';
		}
		if($row['month'] == ($month - 3) ) {
			$template = 'Quarterly';
		}
		if($row['month'] == ($month -  ) {
			$template = 'Quarterly';
		}
			
		$msg .= $row['client_name'] . ' - ' . $row['full_name'] . ' - IFSP Month ' . $row['month'] . ' - ' . $template . '<br/>';
	}
	
	if($is != $row['is_id']){
		if(!empty($msg)){
			echo $msg . ' email sent <br/><br/><br/>';
		}
		$msg = '';
	}
	
	$is = $row['is_id'];
}

[stivenbr]

I just tested everything but I am missing one record per user :/ I don't understand what is wrong with the code :/ 

[ginerjm]

Because you tested for a match instead of a break first.  Follow my example (with the additional output at the end that was pointed out)

 

And WHY DID you double post this problem?