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: 255
Joined: 2018-11-26 13:55
Location: Washington State

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: 157
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

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: 255
Joined: 2018-11-26 13:55
Location: Washington State

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]

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

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: 157
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-02-23 18:24

Hi,

maybe you can post your code so that we can have a look at it.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

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;
		}

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 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'];
	}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

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;
	}

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 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'];
	}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-01 00:33

@ pböttcher,

that did it!! Please explain what that function is. I tried to find it online but there are so many conflicting things said that I have no idea what I just did.

Thanks to all of you for helping me with this......until the next one!!
Ray

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-01 04:24

I may have jumped the gun,

I forgot to comment out the existing array code which is a list of emails entered manually into the hook file. Is there a better way to do this? Should I even be using sendmail?

I am still only getting a single message to go out.

Ray

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-03-01 06:54

Hi,

that sounds very strange as you have 2 addresses in your array. So if you only get one email there must be something else not working.
Can you post the actual code you are using.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-01 15:41

I get the two messages when I use my manually created hooks array, but not when I use the array which involves a query on the database. Whenever I attempt to go that route only the first email address in the table is sent.

When I initially tried the code you provided and got two messages I thought it had worked because I only have two email addresses in the table at this point for testing. But then I realized that what had actually happened was that I had both variable definitions un-commented and apparently the one that is manually written in the code worked. When I commented that variable out (the manual one) only the first email address received a message.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-01 15:59

Here is the current code with the array that calls for a query on the database commented out. As it is configured in this code, the email goes out to the two addresses.

My assumption is that in sendmail the addresses must be comma delimited and have the quotes that are in the manual array. Just guessing here. Is there a way to export the table data to a csv and then use that to populate the array with email addresses??

Thank you for sticking with me on this. I'm betting that others will benefit from this workout!!

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=array
        ($response =sqlValue("SELECT email_address FROM eaddress",$eo));
        	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;
	}

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-03-01 17:18

you did not change the code.

use

Code: Select all

   $receipt=array();
        $response =sql("SELECT email_address FROM eaddress",$eo);
        	while ($row = db_fetch_assoc($reponse )) {
	            $receipt[] = $row['email_address'];
	}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-01 23:11

Actually, I did change the code. It is currently commented out in the sample I included in my prior message above. When I uncomment the code I only get a single email message sent.

When I include the code exactly as written I get no email:

Code: Select all

$receipt=array();
        $response =sql("SELECT email_address FROM eaddress",$eo);
        	while ($row = db_fetch_assoc($reponse )) {
	            $receipt[] = $row['email_address'];
	}
If I modify it as shown here (the way it is currently in my hooks file) I get a single email message:

Code: Select all

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-03-02 06:36

Actually there is a type.

so try

Code: Select all

$receipt=array();
        $response =sql("SELECT email_address FROM eaddress",$eo);
        	while ($row = db_fetch_assoc($response)) {
	            $receipt[] = $row['email_address'];
	}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-02 17:25

Hi Again,

Using that code there is no email message sent.

When I modify it as below, I get a single email. It only sends to the first address in the table.

Code: Select all

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-03-02 18:22

pls check again.

This code should work.

Code: Select all

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

use

Code: Select all

        $receipt=array();
        $response =sql("SELECT email_address FROM eaddress",$eo);
        	while ($row = db_fetch_assoc($response)) {
	            $receipt[] = $row['email_address'];
	            file_put_contents("trace.txt",print_r($receipt,true).PHP_EOL,FILE_APPEND);
	}
and paste the trace.txt file that should be in the main dirctory for your app.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Populate an array from database

Post by rpierce » 2021-03-02 19:40

The code above is working! I get an email to all the addresses in my table. This is great!!

One question....what do you mean by your note?: "and paste the trace.txt file that should be in the main directory for your app."

I see that file (trace.txt) now exists in my main app directory as you said. But what am I supposed to do with it?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Populate an array from database

Post by pbottcher » 2021-03-02 19:42

Hi,

if the code works, you can remove the line with the trace. That was meant to see what happens.

Glad it works now.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply