Email from query from another table

[jayres]

Hi all!

Trying to customize a hook so that after a record is created in a table; it sends an email to a list of email addresses that is queried from another table. The part that I am having trouble with is the query of the addresses to send to.

The table that should be queried is "email_contacts". It should look to see if the field "list_hazardrpt" is set to "Yes"; and if so, pull the email address from the field "email_address to send to. I have looked this up in the forum; but still can't get it to work right. I can get it to send an email to hardcoded addresses; but have problems getting the query from table to work. Below is the code.

Code: Select all

	function rpt_hazardreport_after_insert($data, $memberInfo, &$args){
	foreach($data as $field => $value){


	$messageData .= "$field: $value \n";
	
	$recID=db_insert_id(db_link());
	//Variables for connecting to your database.
//These variable values come from your hosting account.
$hostname = "omitted";
$username = "omitted";
$dbname = "gcoememis";

//These variable values need to be changed by you before deploying
$password = "omitted";
$usertable = "email_contacts";


//Connecting to your database
mysql_connect($hostname, $username, $password) OR DIE ("Unable to
connect to database! Please try again later.");
mysql_select_db($dbname);

$result = mysql_query("SELECT * FROM gcoememis.email_contacts WHERE list_hazardrpt= 'Yes' ");
while($row = mysql_fetch_array($result))
mail($row['email_address'] , 'HAZARD REPORT - Springfield-Greene County OEM' , '"The Springfield-Greene County Office of Emergency Management has just released a new Situation Report.  It can be viewed at the following link".
	  "\n\n".
"http://greenecountyoem.org/opsdesk/gcoem-emis/link.php?t=rpt_hazardreport&f=report_attachment&i=$recID".
  "\n\n".
  "Another Line Of Text"', 'From: [email protected]');
return TRUE;


}
		return TRUE;
	}

Any thoughts?

[a.gneady]

As long as you're using hooks, you shouldn't use direct mysql functions to interface with the database. You shouldn't also connect to the database as there is already an open connection established by your AppGini application. So, no need to use mysql_connect nor mysql_select_db. Also, instead of mysql_query(), you should use sql(), the querying function provided by AppGini.

Also, instead of:

Code: Select all

$recID=db_insert_id(db_link());

you should use

Code: Select all

$recID = $data['selectedID'];

Other than that, your code should work fine --- one issue to be beware of though is the performance of your code ... if your email list is 10 recipients or so, there would be no trouble ... However, you'll see serious performance issues if your list is 400 emails or so.

To optimize your code, you should aggregate your recipients then send them all one email, adding them to the BCC field rather than sending one email to each.

[jayres]

Thanks, will try and see if that works! Had tried it without the connect to database, and kept ketting errors... So that's why I had added.

Ultimately, we will be doing BCC... I have left everything in the "To" field when testing; just to make sure that it was pulling all my testing addresses.

Will report back! Thanks!

[jayres]

Have made the changes; and now not sending any emails at all. Here is what the code looks like now

Code: Select all

	function rpt_hazardreport_after_insert($data, $memberInfo, &$args){
	foreach($data as $field => $value){


	$messageData .= "$field: $value \n";
	
$recID = $data['selectedID'];
	

$result = sql("SELECT * FROM gcoememis.email_contacts WHERE list_hazardrpt = 'Yes' ");
while($row = mysql_fetch_array($result))
mail($row['email_address'] , 'HAZARD REPORT - Springfield-Greene County OEM' , '"The Springfield-Greene County Office of Emergency Management has just released a new Situation Report.  It can be viewed at the following link".
	  "\n\n".
"http://greenecountyoem.org/opsdesk/gcoem-emis/link.php?t=rpt_hazardreport&f=report_attachment&i=$recID".
  "\n\n".
  "Another Line Of Text"', 'From: [email protected]');
return TRUE;


}
		return TRUE;
	}

Any thoughts?

[udayvatturi]

There might be some logic error, try using appgini mailing function.

[jayres]

Uday, how do I do that?


Also, had sent you an email earlier this week also, don't know if you had gotten it...