[RESOLVED] Run a COUNT on each USER and email them the results?

[systm]

Hello,

I am new to this forum and have been struggling to figure out how to approach my issue.

PROBLEM: I have 2 SQL tables, one has all the 'POSTS' (EMPLOYEE ID, SERVICECALL, COMPLETION CODE, DATE) and the other has the 'EMPLOYEES' information (EMPLOYEE ID, FULLNAME, EMAIL) What I need to do is COUNT the number of SERVICECALL records by that employee and email them each their counts.

I have been trying to JOIN the tables, but I have no idea how to loop the query, we have 500+ employees and the query needs to run each time. Is there anyway to do this without 500+ queries? and how do I get the count query to email to that employee only?

I can't seem to wrap my head around this, I know it's a big question but if someone could help me with the PHP/SQL so I at least know where to start.

Thank You!
Ross

[laserlight]

I would expect a query along these lines:

SELECT e.*, COUNT(p.*) AS record_count FROM Employee AS e JOIN Post as p ON e.employee_id=p.employee_id GROUP BY e.employee_id;

[systm]

Thanks, that was what I was thinking. I have my query and it generates a table with EMPLOYEE ID and NUMBER OF CALLS.

I still have no idea how I would run this result through an array? or loop? and email each EMPLOYEE just there COUNT.

Here's what I need to do,

RUN QUERY = # OF CALLS, EMPLOYEE ID, and EMAIL ADDRESS = OUTPUT. <=== how would I take these results and email each row(employee) their (call count) in separate emails?

[laserlight]

I don't see what's the problem: isn't this just your normal processing of a result set? There's nothing special other than the aggregate COUNT, which you can effectively treat as just another column.

Or is your question about how to send an email?

[systm]

Here is my code, it works - SOMEWHAT! 🙂 the issue now is when I run it the array doesn't seem to match up the email, with the tech, and the number of calls posted.

I ran it and got another techs results (They were correct for that person but I got the email instead of them.) this has been my issue all along and I am not too sure why its happening.

<?php


//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(localhost, XXXXX, XXXXXX);
if(!$link) {
    die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(XXXXXXX);
if(!$db) {
    die("Unable to select database");
}

$count = 0; 
$date = date("Y-m-d", $_SERVER['REQUEST_TIME']);

$result = mysql_query("SELECT e. * , COUNT( * ) AS record_count FROM _auth AS e JOIN post AS p ON e.tech = p.tech WHERE date = '$date' AND p.system = '101' GROUP BY e.tech"); 

while ($node = mysql_fetch_object($result)) { 
  //store emails in an array   

  $emailArray[$count] = $node->email; 
  $record_count = $node->record_count;
  $techID = $node->tech;
  $techname = $node->fullname; 
 $count ++; 

}//end while statement 

//email all users in the email array 
		$subject = "$techname | Callpost Report";

	$body = "Dear $techname, <br>";
	$body .="Your daily callpost report for <b>$date</b>.";
	$body .="<br>";
	$body .="<br>";	
	$body .="System Info<br>";
	$body .="------------------------------------<br>";
	$body .="Technician Id:      <b>$techID</b><br>";
	$body .="Total Calls Posted:      <b>$record_count</b><br>";		

	// To send HTML mail, the Content-type header must be set
		$headers = "From: xxxxxxxx <[e-mail]>\r\n";
		$headers  .="BCC: ". $bcc ."\r\n";
        //$headers = 'From: '.$from."\r\n".
        //$headers = "MIME-Version: 1.0\n" ; 
	    $headers .= "Content-Type: text/html; charset=\"iso-8859-1\"\n"; 
	    $headers .= "X-Priority: 1 (Higuest)\n"; 
	    $headers .= "X-MSMail-Priority: High\n"; 
	    $headers .= "Importance: High\n"; 

for ($i = 0; $i < count($emailArray);$i ++) { 

 mail($emailArray[$i], $subject, $body, $headers); 

}
?>

[systm]

Ok I was wrong it does NOT work, looks like it gets stuck on only the first name in the array. so everyone gets the same report, so it looks like the email array is working but the row information isn't.

[bradgrafelman]

That's because you're only storing the data from the last row in the result set. When you loop through the SQL result set:

while ($node = mysql_fetch_object($result)) { 
  //store emails in an array    

  $emailArray[$count] = $node->email; 
  $record_count = $node->record_count; 
  $techID = $node->tech; 
  $techname = $node->fullname; 
$count ++; 

}//end while statement

You keep overwriting the values of the variables $record_count, $techID, and $techname with the current row's values without ever doing anything with the previous row's values.

However, rather than store the data from all of the rows, why not just send out each e-mail as you loop through the SQL result set?

Also, your e-mail headers look suspicious. Why do you suddenly switch from using CRLF line endings to only LF line endings?

EDIT: Forgot to mention.. when posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

[systm]

Could you show me the correct way of doing this, I understand what you're saying but my PHP programming isn't the greatest. I know my headers are suspicious, I haven't had time to look at that yet, they do work though 🙂

[johanafm]

systm;10983097 wrote:

$headers = 'From: '.$from."\r\n".
$headers = "MIME-Version: 1.0\n" ;

Once of these headers ends with CRLF (correct), the other ends with LF (incorrect). Do note that according to the RFCs for SMTP, at least up until 2008, specified that lines (more or less) end with CRLF and that

The receiver will take no action until this sequence is received.

Which means that a conforming agent might treat two or more of your lines as one line possibly leading to being unable to relay your email.

What is e.tech? You should most likely go with laserlights recommendation and use e.employee_id.

Moreover, since the SQL standard specifies that

When a group by clause is present, every field has to be either part of the group by clause or be part of an aggregate function.

and due to this, most SQL implementations will not allow you to violate this, for good reasons. And while MySQL will let you violate this, doing so will often lead to unpredictable results, so you should most definitely

SELECT e.field1, e.field2, e.employee_id, ...
...
GROUP BY e.employee_id, e.field1, e.field2

rather than use SELECT e. GROUP BY one_single_field_from_e. It would obviously suffice to

SELECT e.*, ...
...
GROUP BY e.every, e.single, e.field, e.from, e.table_e

But if you later add a new field to e, your query will once again be invalid SQL.

[systm]

good point, I was referencing that wrong. thanks! I am still not sure how the php side of this would look/work as I know my current way violates SQL and could yield unpredictable results, I just been at this for over a week now and I can't seem to figure out how to script this. any help?

[johanafm]

systm;10983168 wrote:

any help?

Yes, make changes according to my last post. Then try running the script again. If things don't work or you are uncertain about some specific thing I recommended, ask specific questions and post an updated version of your code and any error messages.

[systm]

<?php

//Connect to mysql server
$link = mysql_connect(localhost, xxxxxxxx, xxxxxxx);
if(!$link) {
    die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(xxxxxxxx);
if(!$db) {
    die("Unable to select database");
}

$count = 0; 
$date = date("Y-m-d", $_SERVER['REQUEST_TIME']);

$result = mysql_query("SELECT p.tech, p.date, e.email, e.fullname, COUNT( * ) AS record_count FROM _test AS e JOIN post AS p ON e.tech = p.tech WHERE DATE =  '2011-06-26' AND p.system =  '101' GROUP BY p.tech, e.fullname"); 

while ($node = mysql_fetch_object($result)) {
//while ($node = mysql_fetch_object($result)) { 
  //store emails in an array     

  $emailArray[$count] = $node->email; 
  $record_count = $node->record_count; 
  $techID = $node->tech; 
  $techname = $node->fullname; 
$count ++; 

}//end while statement 

//email all users in the email array 
		$subject = "$techname | Callpost Report";

	$body = "Dear $techname, <br>";
	$body .="Your daily callpost report for <b>$date</b>.";
	$body .="<br>";
	$body .="<br>";	
	$body .="System Info<br>";
	$body .="------------------------------------<br>";
	$body .="Technician Id:      <b>$techID</b><br>";
	$body .="Total Calls Posted:      <b>$record_count</b><br>";
	$body .="System Name:    fieldserv_port01-1<br>";


	// To send HTML mail, the Content-type header must be set
		$headers = "From: xxxxxxxx <[e-mail]>\r\n";
	    $headers .= "Content-Type: text/html; charset=\"iso-8859-1\"\n"; 
	    $headers .= "X-Priority: 1 (Higuest)\n"; 
	    $headers .= "X-MSMail-Priority: High\n"; 
	    $headers .= "Importance: High\n"; 

for ($i = 0; $i < count($emailArray);$i ++) { 

 mail($emailArray[$i], $subject, $body, $headers); 

}
?>

Still sending only first row info to everyone and not looping, can't figure out how to loop this, the query IS much better structured as oppose to e.* thanks for that tip (did I do it right?)

[johanafm]

systm;10983173 wrote:

Still sending only first row info to everyone and not looping

That's because you store the email addresses in an array, but still overwrite all other info to the same 3 variables.

systm;10983173 wrote:

the query IS much better structured as oppose to e.* thanks for that tip (did I do it right?)

Yes, but you still lack a few fields in the group by clause.

# I'm guessing this date should also be used in the query...
$date = date("Y-m-d", $_SERVER['REQUEST_TIME']);

# which means you might do something like this to make use of $date
$qry = sprintf(
		"SELECT p.tech, p.date, e.email, e.fullname, COUNT( * ) AS record_count
		FROM _test AS e
		JOIN post AS p ON e.tech = p.tech
		WHERE DATE =  '%s' AND p.system =  '101'
		GROUP BY p.tech, e.fullname, e.email, p.date",
		$date);
/* But do note that you still don't have every field which is not part of an aggregate
 * function in the group by clause. Missing fields: p.date, e.email
 * Add these to the group by clause like I've done here
 */

# mysql_query returns EITHER a result set resource, or boolean false
$result = mysql_query($qry);
/* Yet you do not check what the returned value is before calling mysql_fetch_object
 * which does NOT take boolean false as an argument.
 */
if (!$result)
{
	# something went wrong...
	error_log(sprintf('%d: %s', mysql_errno(), mysql_error());
	echo 'Unable to retrieve callpost report';
}
else
{
	/* You were storing the email address in an array
	 * while still overwriting techId and techname.
	 * Either store everything as an array, the simplest way
	 * 		being $row[] = $node;
	 * and thereafter when sending the email
	 * $row[$i]->email, $row[$i]->fullname etc
	 * or do as previously suggested by brad and send the email in the initial while loop.
	 * Since the email headers don't change, set them once before the loop.
	 * And make sure you end every header field with \r\n (CRLF) not just \n
	 */

# correct: uses \r\n
$headers = "From: xxxxxxxx <[e-mail]>\r\n";
# missing CR
$headers .= "Content-Type: text/html; charset=\"iso-8859-1\"\n"; 
# missing CR
$headers .= "X-Priority: 1 (Higuest)\n"; 
# missing CR
$headers .= "X-MSMail-Priority: High\n"; 
# missing CR
$headers .= "Importance: High\n"; 
while ($node = mysql_fetch_object($result))
{
	$subject = sprintf('%s | Callpost Report', $node->fullname);

	$body = sprintf(
'Dear %s, <br>
Your daily callpost report for <b>%s</b>.
<br>
<br>	
System Info<br>
------------------------------------<br>
Technician Id:      <b>%d</b><br>
Total Calls Posted:      <b>%d</b><br>
System Name:    fieldserv_port01-1<br>',
			$node->fullname,
			$date,
			$node->tech,
			$node->record_count
		);

	if (!mail($node->email, $subject, $body, $headers))
	{
		error_log(
			sprintf('failed sending email report to %s (%d)',
				$node->fullname,
				$node->tech)
		);
	}
}//end while statement 
}

[systm]

getting "Parse error: syntax error, unexpected T_ECHO in /home/systm/public_html/sjrb/_developer/test2.php on line 20"

[laserlight]

systm wrote:

getting "Parse error: syntax error, unexpected T_ECHO in /home/systm/public_html/sjrb/_developer/test2.php on line 20"

A simple typographical error that you should be able to debug by matching parentheses 🙂

[systm]

YUP! 🙂 I just figured it out, and it is working now (I believe? I am getting reports from employees now that their info is matching up.) Will let you know after a few more tests.

this approach makes sense, and it's been quite the learning curve for me. Thank you all for the help and patience, greatly appreciated.

Will tag as resolved shortly