Jump to content

Query Help Grouping


stivenbr

Recommended Posts

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 by stivenbr
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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'];
}
Link to comment
Share on other sites

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
?>
Link to comment
Share on other sites

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'];
}
Link to comment
Share on other sites

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'];
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.