Populate an array from database

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Populate an array from database

Post by rpierce » 2021-02-22 18:31

Hi all,

I'm using an array in Hooks to send an email to specified recipients when a form is submitted. Currently I have the email addresses of the recipients stored in the array within the hooks file:

Code: Select all

function tablename_before_insert

$receipt=array('[email protected]','[email protected]');

foreach ($receipt as $address){

Contents of email here and other things here.

}
Is there a way to populate this array using data from a table that is input by a user?

Thanks for any help.

pfrumkin
Veteran Member
Posts: 91
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: Populate an array from database

Post by pfrumkin » 2021-02-22 21:45

Hi,

It's not clear, is this a one time thing or an ongoing thing? If it is ongoing and you need to maintain a distribution list, then is it unique for each account, or a common list used by all? If it's a one time thing, you could use a column in this table and have users add the email addresses with a delimiter, like comma or semi-colon, then iterate through that field.

Do you want to send the email all at once (a blast), or is it important that each recipient get their own email?

~Paul

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1200
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pböttcher » 2021-02-22 22:18

Hi,

you can try something like:

Code: Select all

	$result =sql("SELECT email FROM tablename WHERE yourcondition");
	while ($row = db_fetch_assoc($result )) {
	    $receipt[] = $row['email'];
	}
replace email, tablename, yourcondition to your needs.

rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Re: Populate an array from database

Post by rpierce » 2021-02-22 23:47

Hi Paul,

Thank you for the questions.

What I'm doing is trying to design the system so that admin users (not super admin) of the system can add and delete email addresses as projects change. (construction projects) If there is an incident or injury event and a report is completed using the App, certain principals must be notified. When the user of my app moves on to a new project, or managers change on the existing project, they will need to change the recipient list accordingly.

The email can go out in a blast.

Thanks again,
Ray

rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Re: Populate an array from database

Post by rpierce » 2021-02-23 00:07

Hi pbottcher,

I am including the entire hook code in hopes you will show me how to implement your suggestion.

Thank you for offering your help.
Ray

[code

Code: Select all

	function incident_after_insert($data, $memberInfo, &$args){
	    
	    // to compose a message containing the submitted data,
        // we need to iterate through the $data array
        foreach($data as $field => $value){
        $messageData .= "$field: $value \n";
    }
    $authName = sqlValue("select custom1 from membership_users where memberID='{$memberInfo['username']}'");
    
    $projectname = sqlValue("select name from project where id='{$data['project']}'");
    
$receipt=array('[email protected]','[email protected]');
    
    $msgTEXT = "The following Incident Report was submitted by $authName.<br> This is a preliminary report, details may change."."

<p><strong>Project: </strong>$projectname
<p><strong>Date of incident: </strong>{$data['date']}
<p><strong>Time of incident: </strong>{$data['time']}
<p><strong>Type of Incident: </strong>{$data['type']}
<p><strong>Incident Summary: </strong>{$data['summary']}
<p><strong>How did it happen: </strong>{$data['how']}
<p><strong>Objects involved: </strong>{$data['object']}
<p><strong>What were people doing: </strong>{$data['activity']}
<p><strong>Comments: </strong>{$data['comments']}
<p><strong>Witnesses: </strong>{$data['witness']}
<p><strong>Behavior Based Safety Factor: </strong>{$data['bbs']}";
    
    
  	foreach ($receipt as $address){
    
    sendmail(array(
	
		// mail recipient
		'to' => $address,
		
		'name' => "",

		// subject
		'subject' => "An incident report has been submitted for " . sqlValue("select name from project where id='{$data['project']}'"),
		
		// message
		'message' => nl2br (stripcslashes($msgTEXT)),
        
    // sender address
        "From: [email protected]"
    ));
	}
    
    //end email code

		return TRUE;
	}
][/code]

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1200
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pböttcher » 2021-02-23 07:14

Hi Ray,

as I do not know where you have the information about the recipients stored I can only provide the generic code. But with that you shall be able to replace your code. I already set it up so that it should work if you fill your correct table and fieldnames.

rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Re: Populate an array from database

Post by rpierce » 2021-02-23 16:21

@ pbottcher,

Hi again. I added the code you provided with the necessary changes and managed to have no error messages but the email did not send. I assume that I have the code in the wrong place??

pfrumkin
Veteran Member
Posts: 91
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: Populate an array from database

Post by pfrumkin » 2021-02-23 17:23

Hi Ray,

I don't know how to really run a debugger in PHP, much less when it's inside AG. I go old school, open a file, write statements/variable value, to see what is happening. I would put an fopen before the foreach, then within the loop fwrite the different variable values. This also tells you if you are even in your foreach. You'll need an fclose after the foreach.

All of that said, since you said it could be a blast I strongly recommend that you build a single string with all of the recipients (with delimiter comma I think), and send one email. That is less traffic for your email router.

~Paul

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1200
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pböttcher » 2021-02-23 18:24

Hi,

maybe you can post your code so that we can have a look at it.

rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Re: Populate an array from database

Post by rpierce » 2021-02-23 19:23

Hello,
The entire hook "incident after insert" is included here. The array code has been commented out for testing of the new code you have suggested. The array works fine, but I want to be able to allow the end user to specify the email addresses so that I don't have to go into the hooks and update it every time they want a change in recipients. I created a table in the database called 'eaddress' which has a row called 'email_address'.

As it is now with the code below, there is no error message or indications in the PHP editor of something wrong, but when the form is submitted no email is sent.

As I may have mentioned in prior posts, I'm very new and green at PHP. Anything outside the very basics stumps me at this point. But, I've made headway in some understanding thanks to the help of this forum.

Thank you for your time.
Ray

Code: Select all

	function incident_after_insert($data, $memberInfo, &$args){
	    
	    // to compose a message containing the submitted data,
        // we need to iterate through the $data array
        foreach($data as $field => $value){
        $messageData .= "$field: $value \n";
    }
    $authName = sqlValue("select custom1 from membership_users where memberID='{$memberInfo['username']}'");
    
    $projectname = sqlValue("select name from project where id='{$data['project']}'");
    
    $receipt =sqlValue("SELECT email_address FROM eaddress");
	while ($row = db_fetch_assoc($receipt )) {
	    $receipt = $row['email_address'];
	}


	
/*$receipt=array('[email protected]',[email protected]);*/
    
    $msgTEXT = "The following Incident Report was submitted by $authName.<br> This is a preliminary report, details may change."."

<p><strong>Project: </strong>$projectname
<p><strong>Date of incident: </strong>{$data['date']}
<p><strong>Time of incident: </strong>{$data['time']}
<p><strong>Type of Incident: </strong>{$data['type']}
<p><strong>Incident Summary: </strong>{$data['summary']}
<p><strong>How did it happen: </strong>{$data['how']}
<p><strong>Objects involved: </strong>{$data['object']}
<p><strong>What were people doing: </strong>{$data['activity']}
<p><strong>Comments: </strong>{$data['comments']}
<p><strong>Witnesses: </strong>{$data['witness']}
<p><strong>Behavior Based Safety Factor: </strong>{$data['bbs']}";
    
    
  	foreach ($receipt as $address){
    
	sendmail(array(
	
		// mail recipient
		'to' => $address,
		
		'name' => "",

		// subject
		'subject' => "An incident report has been submitted for " . sqlValue("select name from project where id='{$data['project']}'"),
		
		// message
		'message' => nl2br (stripcslashes($msgTEXT)),
        
    		// sender address
 	       "From: [email protected]"
  		 ));
		}
    
   		//end email code

		return TRUE;
		}

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1200
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pböttcher » 2021-02-23 20:09

Hi,

you are overwriting the $receipt variable and you are not assigning the data to an array

Code: Select all

$receipt=array();
   $response =sqlValue("SELECT email_address FROM eaddress");
	while ($row = db_fetch_assoc($reponse )) {
	    $receipt[] = $row['email_address'];
	}

rpierce
Veteran Member
Posts: 45
Joined: 2018-11-26 13:55

Re: Populate an array from database

Post by rpierce » 2021-02-23 22:53

Great!! Forward progress!! Initially I did not get the email after submitting the form. I modified the code a tiny bit and email came through, but only the first address in the table got an email. I assume that I somehow need to get a comma delimiter into the code somehow?? I tried adding a comma in this part of the code and still got only the single email. $row['email_address,'] I have included the entire hook again with the changes I have made.

Code: Select all

	function incident_after_insert($data, $memberInfo, &$args){
	    
	    // to compose a message containing the submitted data,
        // we need to iterate through the $data array
        foreach($data as $field => $value){
        $messageData .= "$field: $value \n";
    }
    $authName = sqlValue("select custom1 from membership_users where memberID='{$memberInfo['username']}'");
    
    $projectname = sqlValue("select name from project where id='{$data['project']}'");
    
   $receipt=array
        ($response =sqlValue("SELECT email_address FROM eaddress"));
        	while ($row = db_fetch_assoc($reponse )) {
	            $receipt[] = $row['email_address'];
	}


/*$receipt=array('[email protected]','[email protected]');*/
    
    $msgTEXT = "The following Incident Report was submitted by $authName.<br> This is a preliminary report, details may change."."

<p><strong>Project: </strong>$projectname
<p><strong>Date of incident: </strong>{$data['date']}
<p><strong>Time of incident: </strong>{$data['time']}
<p><strong>Type of Incident: </strong>{$data['type']}
<p><strong>Incident Summary: </strong>{$data['summary']}
<p><strong>How did it happen: </strong>{$data['how']}
<p><strong>Objects involved: </strong>{$data['object']}
<p><strong>What were people doing: </strong>{$data['activity']}
<p><strong>Comments: </strong>{$data['comments']}
<p><strong>Witnesses: </strong>{$data['witness']}
<p><strong>Behavior Based Safety Factor: </strong>{$data['bbs']}";
    
    
  	foreach ($receipt as $address){
    
    sendmail(array(
	
		// mail recipient
		'to' => $address,
		
		'name' => "",

		// subject
		'subject' => "An incident report has been submitted for " . sqlValue("select name from project where id='{$data['project']}'"),
		
		// message
		'message' => nl2br (stripcslashes($msgTEXT)),
        
    // sender address
        "From: [email protected]"
    ));
	}
    
    //end email code

		return TRUE;
	}

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1200
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pböttcher » 2021-02-26 22:09

Hi,

try changing

Code: Select all

   $receipt=array
        ($response =sqlValue("SELECT email_address FROM eaddress"));
        	while ($row = db_fetch_assoc($reponse )) {
	            $receipt[] = $row['email_address'];
	}
to

Code: Select all

   $receipt=array();
        $response =sql("SELECT email_address FROM eaddress",$eo);
        	while ($row = db_fetch_assoc($reponse )) {
	            $receipt[] = $row['email_address'];
	}

Post Reply