Announcement

Collapse
No announcement yet.

[SOLVED] Email array

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SOLVED] Email array

    I have a from with an onAfterInsert event with the following code where I'm trying to send results of a field lookup to an array for mail_to:

    PHP Code:
    /**
    * Send an email with attachment
    */

    // Email parameters
    $mail_smtp_server 'mail.xxx.com';        // SMTP server name or IP address
    $mail_smtp_user   'admin';                   // SMTP user name
    $mail_smtp_pass   'admin';                // SMTP password
    $mail_from        'admin@xxx.com';          // From email
    $emails_roster = array('[mail_to]');
    $mail_to $emails_roster;

    $mail_subject     'Test message';            // Message subject
    $mail_message     'This is a test message.'// Message body
    $mail_format      'H';                       // Message format: (T)ext or (H)tml

    // Send email
    sc_mail_send($mail_smtp_server,
                 
    $mail_smtp_user,
                 
    $mail_smtp_pass,
                 
    $mail_from,
                 
    $mail_to,
                 
    $mail_subject,
                 
    $mail_message,
                 
    $mail_format); 

    I have a field as type multiple select and another field with this query:
    Code:
    select email from employees where emp_id = {roster}
    Employees selected in the multiple select field are ajax reloaded into another field that show people email address who are on the right side of the multi select. That works great and I can send emails to multiple recipients with an array. However to use the array I have to hard code the email addresses in. What I need it to push the email values retrieved from the filed pulling them from the multi select and send mail. This way mail is sent to only who ever I choose and not whoever happens to be hard coded in. I've tried using the {field} and now I'm saving the field as a variable to [mail_to]. Not too good with the PHP and there are more PHP array functions than I can shake a stick at. Anyone know how to achieve this? I may have been vague so if more info is needed let me know.

  • #2
    Have a look at the explode() function.

    jsb

    Comment


    • #3
      Thank you jsbinca. I was taking a different approach and that function came up. I eliminated the mail_to field and am doing the email look up at the same time on the roster field. I have it successfully putting the email address selected into one record separated by a comma. I had tried a semi colon but SQL didn't like that when looking up the record. It only liked comma separated values. So, I was looking at a way to replace the commas with semi colons for the recipient string, which is the opposite of SQL and likes the semi colon over the comma. I found an example of explode(preg_replace) that was supposed to achieve that but when I echo the result of the explode I get the words "Array". I'm so close!!

      Comment


      • #4
        Hi,
        if you want SQL (MySQL?) to deliver one record have a look at GROUP_CONCAT().
        Should look like: SELECT GROUP_CONCAT(email SEPARATOR ';') FROM employees WHERE emp_id = {roster};
        That should do it.

        jsb

        Comment


        • #5
          @ancr2001:

          Whats exact your problem?

          You have a query:

          $sql = "select email from employees where emp_id = " . {roster}

          This give you the email adresses in a recordset:

          sc_lookup(rs, $sql)

          rs is an array that contains the data. The semikolon separated string:

          $email = implode (";", {rs})

          Thats all ...
          Best regards: - Reinhard -

          I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

          Comment


          • #6
            @RHS

            I put this off awhile back and now a new project demands this functionality. I've tried the implode function several different ways but am still having problems. Another post mentioned using a foreach or while loop to fetch the email address from the db to an array. From what I understand a recordset is not the exact same as an array and the recordsets in SC are proprietary functions. Here's the code from my new issue:

            PHP Code:
            $check_sql "SELECT smtp_server, smtp_user, smtp_pass, mail_from"
               
            " FROM conf_smtp_server"
               
            " WHERE comp_id = '" . [usr_comp_id] . "'";
            sc_lookup(rs$check_sql);

            if (isset({
            rs[0][0]}))     // Row found
            {
                    
            $mail_smtp_server = {rs[0][0]};
                    
            $mail_smtp_user = {rs[0][1]};
                    
            $mail_smtp_pass = {rs[0][2]};
                    
            $mail_from = {rs[0][3]};
            }


            $check_sql2 "SELECT subject, body"
               
            " FROM email_config"
               
            " WHERE type = '" . {config_id} . "' AND comp_id = '" . [usr_comp_id] . "'";
            sc_lookup(rs$check_sql2);

            if (isset({
            rs[0][0]}))     // Row found
            {
                
            $mail_subject = {rs[0][0]};
                
            $mail_message ={rs[0][1]};

            }

            $check_sql3 "SELECT e.email"
               
            " FROM employees e"
               
            " JOIN email_notifications n on e.emp_id=n.emp_id"
               
            " WHERE n.email1 = '1'";
            sc_lookup(rs$check_sql3);

            if (isset({
            rs[0][0]}))     // Row found
            {
               
            //{rs} = array();
                //$mail_to = implode(";",array());
                
            $mail_to implode(";",'{rs}');
            }

            $mail_format      'H';                     

            // Send email";
            sc_mail_send($mail_smtp_server,
                         
            $mail_smtp_user,
                         
            $mail_smtp_pass,
                         
            $mail_from,
                         
            $mail_to,
                         
            $mail_subject,
                         
            $mail_message,
                         
            $mail_format); 
            From the code above you can see that I'm getting email addresses joined to another table that has flags allowing/disallowing certain types of emails to be sent. If email1 field for a particular employee is a 1 then they get that email if 0 then they don't. This code works great if only one employee is allowed to get this email. As soon as I enable another employee to receive this email my problem starts. As you can see I've been fiddling around with the $mail_to variable trying to get this to work! Most of the time I get Array instead of the value in {rs}. I either get RFC errors because it doesn't recognize the word Array or I get implode invalid argument supplied. In case you haven't noticed I have a limited grasp on all this so some hand holding will be needed

            Comment


            • #7
              $mail_to = implode(";", {rs}); but not $mail_to = implode(";", '{rs}'); ...

              HTH.
              Best regards: - Reinhard -

              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

              Comment


              • #8
                I've tried every variation of syntax with (), "", and ''. I originally had used $mail_to = implode(";", {rs}); and I still get the word Array in the mail_to variable and an RFC 2822 error. I've also had array to string conversion errors which I don't understand because I'm using the implode function to do the conversion.

                Comment


                • #9
                  What the structure from the tables "employees" and "email_notifications"?
                  Best regards: - Reinhard -

                  I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                  Comment


                  • #10
                    I'm starting to understand how the array works and is navigated but I just can't figure out how to get what's in the array instead of "Array".


                    employees
                    Code:
                    CREATE TABLE `employees` (
                      `id` int(10) NOT NULL AUTO_INCREMENT,
                      `emp_id` int(10) unsigned NOT NULL,
                      `active` varchar(1) NOT NULL,
                      `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
                      `fname` varchar(64) NOT NULL,
                      `space` varchar(3) NOT NULL,
                      `lname` varchar(32) NOT NULL,
                      `comp_id` int(10) unsigned NOT NULL,
                      `group_id` int(10) unsigned NOT NULL,
                      `email` varchar(64) DEFAULT NULL,
                      `ticket_staff` char(1) NOT NULL,
                      `full_name` varchar(96) DEFAULT NULL,
                      `email_admin` tinyint(1) unsigned DEFAULT NULL,
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `emp_id` (`emp_id`) USING BTREE,
                      KEY `n_compid` (`comp_id`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1

                    email_notifications
                    Code:
                    CREATE TABLE `email_notifications` (
                      `emailid` int(10) unsigned NOT NULL AUTO_INCREMENT,
                      `email1` tinyint(1) unsigned DEFAULT '0',
                      `email2` tinyint(1) unsigned DEFAULT '0',
                      `email3` tinyint(1) unsigned DEFAULT '0',
                      `email4` tinyint(1) unsigned DEFAULT '0',
                      `email5` tinyint(1) unsigned DEFAULT '0',
                      `email6` tinyint(1) unsigned DEFAULT '0',
                      `email7` tinyint(1) unsigned DEFAULT '0',
                      `email8` tinyint(1) unsigned DEFAULT '0',
                      `email9` tinyint(1) unsigned DEFAULT '0',
                      `email10` tinyint(1) unsigned DEFAULT '0',
                      `email11` tinyint(1) unsigned DEFAULT '0',
                      `email12` tinyint(1) unsigned DEFAULT '0',
                      `email13` tinyint(1) unsigned DEFAULT '0',
                      `email14` tinyint(1) unsigned DEFAULT '0',
                      `email15` tinyint(1) unsigned DEFAULT '0',
                      `email16` tinyint(1) unsigned DEFAULT '0',
                      `email17` tinyint(1) unsigned DEFAULT '0',
                      `emp_id` int(10) unsigned DEFAULT NULL,
                      PRIMARY KEY (`emailid`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1

                    Comment


                    • #11
                      Use ...
                      Code:
                      $mail_to = {rs[0][0]};
                      You have select a single line field "email", thats not an array, only a text field.
                      Best regards: - Reinhard -

                      I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                      Comment


                      • #12
                        I had something similar to you and was pulling my hair out as I just couldn't see the problem.

                        I keep multiple configs of SMTP settings in a MySQL table, and pull them out in a library function based on a "friendly" name.

                        The SQL I was using worked when in phpmyadmin so I was confident it was okay. The below function showed, in my case, that I was getting an SQL error because I had field names that were ambiguous in my particular case. And so I was just getting "Array" returned when examining it for the problem. I show the whole function for context, but the key bit is examining if the array is un-initilaised (as opposed to simply empty) - using the "_erro" suffix. So you may well find your SQL is not behaving as you expect.


                        Code:
                        	// Get email settings based on friendly name (returns array)
                        
                        	function get_smtp_details($smtp_name) {
                        		$ssql = "SELECT ".
                        					"server_email, ".
                        					"user_email, ".
                        					"pass_email, ".
                        					"from_email, ".
                        					"port_email, ".
                        					"ssl_email, ".
                        					"format_email ".
                        				"FROM ".
                        					"tbl_smtp ".
                        				"WHERE ".
                        					"friendly_name = '" . $smtp_name . "'";
                        		
                        		sc_lookup(rs, $ssql);
                        
                        		if ({rs} === false) {
                        			echo "Access error. Message=". {rs_erro} ;              // This is the key test!!!!
                        		
                        		} elseif (count({rs}) == 0) {
                        			sc_error_message("No SMTP config for '" . $smtp_name . "' found.");
                        			sc_error_exit();
                        		
                        		} else {
                        			return {rs};
                        		}
                                }

                        Comment


                        • #13
                          When I use $mail_to = {rs[0][0]}; it works fine but only sends to the email address in [0][0]. I need it to send emails to multiple users. I have confirmed that the sql for it all is correct. I tried using the error test that adz1111 sugested but I get a full page of code in debug mode but no errors. If I query it in a grid both of the email addresses of the users who are allowed the email addresses show up. I tried doing that in a field on the form but only one comes in even with multiple values checked and delimiter defined. As long as one employee is set on a certain email it works but as soon as a second employee is set on the same email then it doesn't work. How do I loop through {rs} to get [0][0],[0][1],[0][2],....?

                          Comment


                          • #14
                            Originally posted by RHS View Post
                            Use ...
                            Code:
                            $mail_to = {rs[0][0]};
                            You have select a single line field "email", thats not an array, only a text field.
                            I understand that the one field is not an array but using macros require recordset which then turns it into a fake array and I have my problem.

                            I even tried straight PHP/SQL to no avail:
                            Code:
                            $con = mysql_connect("reporter","root","ajatnk","csscompliance");
                            $db_selected = mysql_select_db("csscompliance",$con);
                            
                            $query_mailset = "SELECT e.email from employees e join email_notifications n on e.emp_id = n.emp_id where n.email1 = '1'";
                            
                            $mailset = mysql_query($query_mailset, $db_selected) or die(mysql_error());
                            if($mailset === FALSE) {
                                die(mysql_error()); 
                            }
                            $row_mailset = mysql_fetch_assoc($mailset);
                            
                            $totalRows_mailset = mysql_num_rows($mailset);
                            
                            
                            while ($mail = mysql_fetch_assoc($mail_set)){
                            
                            extract ($mail);
                            }
                            Last edited by ancr2001; 09-11-2014, 10:41 AM.

                            Comment


                            • #15
                              Ok, try this

                              Code:
                              $check_sql3 = "SELECT e.email" 
                                 . " FROM employees e" 
                                 . " JOIN email_notifications n on e.emp_id=n.emp_id" 
                                 . " WHERE n.email1 = '1'"; 
                              
                              sc_select(dataset, $check_sql3); 
                              
                              if ({dataset} === false)
                              {
                                 // sc_erro_mensagem("An error occurred in access to the database.<BR>");
                              }
                              else 
                              {
                              
                              $mail_to = "";
                              
                              while (!$dataset->EOF) 
                              {
                                 $mail_to = $mail_to . $dataset->fields[0] . "; ";
                              }
                              This reads all email adresses and generate a string ala "info@domain1.com; info@domain2.com; ...".
                              Best regards: - Reinhard -

                              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                              Comment

                              Working...
                              X