How can i compare datas with sql query?

Scriptcase 9
#1

Hi there,

I’m using the latest version of the Scriptcase. Now i’m trying to finish my project but i have a problem;

I’m using mysql on my computer. Now just think there is 2 tables;

  • periodic_maintenance
  • employer_staff

There is a form for employer_staff, so i insert 3 person to here. This table has this fields;

  • Name
  • Sirname
  • Email address

There is another form for periodic_maintenance and it has a fiedl that i can select employer_staff (i can select multi) as name and surname.

I want to send emails to selected employer_staffs. So i’m using this codes in the “onAfterInsert” side;

$sending= "";

sc_select(my_data, "select id_employer_staff, email from employer_staff where '{employer_staff}' = id_employer_staff");
if ({my_data} === false)
	{
	echo "Access error. Message =". {my_data_erro};
} else {
	while (!{my_data}->EOF)
		{
		$sending= $sending . {my_data}->fields[1] . ";";
		{my_data}->MoveNext();
	}
	{my_data}->Close();
}
		  
$mail_smtp_server    = 'mail.google.com';
$mail_smtp_user      = 'onderxyilmaz@gmail.com';
$mail_smtp_pass      = 'my_gmail_password';
$mail_from           = 'onderxyilmaz@gmail.com';
$mail_to             = 'onderxyilmaz@gmail.com';
$mail_subject        = 'Who will get this email';
$mail_message        = 'Mail gönderilecek kişiler = ' . $sending;
$mail_format         = 'H';
$mail_copies         = '';
$mail_tp_copies      = '';
$mail_port           = '465';
$mail_tp_connection  = 'S';

sc_mail_send($mail_smtp_server,
			 $mail_smtp_user,
             $mail_smtp_pass,
			 $mail_from,
			 $mail_to,
			 $mail_subject,
			 $mail_message,
			 $mail_format,
			 $mail_copies,
			 $mail_tp_copies,
			 $mail_port,
			 $mail_tp_connection);

It send the email but only the first employer_staff, not all selected. How can i solve this problem?

Best regards.

#2

well i’m not sure, but have you tried to change the ; to , also the mail_to is a solid mailadres not a var

1 Like
#3

Does the select statement return more than one record? If so the sending of emails is outside the while loop

1 Like
#4

In the while loop you are populating the recipient list and storing it in the $sending variable.
But you then have hard coded this var
$mail_to = ‘onderxyilmaz@gmail.com’;
And you use it in the sc_mail_send macro

Just this correction should make your code work:

sc_mail_send($mail_smtp_server,
$mail_smtp_user,
$mail_smtp_pass,
$mail_from,
$sending,
$mail_subject,
$mail_message,
$mail_format,
$mail_copies,
$mail_tp_copies,
$mail_port,
$mail_tp_connection);

1 Like
#5

Yes you’re right, thanks but it’s not problem for now. My problem is about the loop.

#6

This is the problem, it’s return just one record, not anymore. Maybe my loop is not correct?

#7

I guess you mean i have to send each e-mail as a loop. It’s a good idea but this is not the problem. In my example i’m trying get each records to my variable and then send it to email’s message. But i got the message with 1 record, not all records. So i guess my loop is not correct. Any idea?

#8

Try this
$sending= “”;
$mail_smtp_server = ‘mail.google.com’;
$mail_smtp_user = ‘onderxyilmaz@gmail.com’;
$mail_smtp_pass = ‘my_gmail_password’;
$mail_from = ‘onderxyilmaz@gmail.com’;
$mail_to = ‘onderxyilmaz@gmail.com’;
$mail_subject = ‘Who will get this email’;
$mail_message = 'Mail gönderilecek kişiler = ’ . $sending;
$mail_format = ‘H’;
$mail_copies = ‘’;
$mail_tp_copies = ‘’;
$mail_port = ‘465’;
$mail_tp_connection = ‘S’;

sc_select(my_data, “select id_employer_staff, email from employer_staff where ‘{employer_staff}’ = id_employer_staff”);
if ({my_data} === false)
{
echo “Access error. Message =”. {my_data_erro};
} else {
while (!{my_data}->EOF)
{
$mail_to = {my_data}->fields[1];
{my_data}->MoveNext();
sc_mail_send($mail_smtp_server,
$mail_smtp_user,
$mail_smtp_pass,
$mail_from,
$mail_to,
$mail_subject,
$mail_message,
$mail_format,
$mail_copies,
$mail_tp_copies,
$mail_port,
$mail_tp_connection);
}

{my_data}->Close();

}

#9

The issue could be with the where clause:

where ‘{employer_staff}’ = id_employer_staff

Is the {employer_staff} the multi select field?

If so, echo the {employer_staff} var and check how the multiple values are separated.

Because with multiple IDs your where clause could become something like this (assuming multiple values get comma separated):

where ‘5,8,10’ = id_employer_staff

If I remember correctly with comma separated values and mysql/mariadb you could just use IN as an operator

where id_employer_staff IN ({employer_staff})

1 Like
#10

Thanks again, i tried it but it send a blank.

#11

This is awesome. Thank you so much. I tried and it’s working. Many thanks.

Now i see email addresses like a@b.com;c@d.com;

So there is a semicolumn at the end of the line. How can i remove it? Any idea?

#12

Sorry i found it. I added this code;

$sending= substr_replace($sending,"", -1);

Now my latest code is here;

$sending= “”;

sc_select(my_data, “select id_employer_staff, email from employer_staff where id_employer_staff IN ({employer_staff })”);
if ({my_data} === false)
{
echo “Access error. Message =”. {my_data_erro};
} else {
while (!{my_data}->EOF)
{
$sending= $sending. {my_data}->fields[1] . “;”;
{my_data}->MoveNext();
}
{my_data}->Close();
}

$sending= substr_replace($sending,"", -1);

$mail_smtp_server = ‘mail.google.com’;
$mail_smtp_user = ‘onderxyilmaz@gmail.com’;
$mail_smtp_pass = ‘my_gmail_password’;
$mail_from = ‘onderxyilmaz@gmail.com’;
$mail_to = ‘onderxyilmaz@gmail.com’;
$mail_subject = ‘Who will get this email’;
$mail_message = 'Mail gönderilecek kişiler = ’ . $sending;
$mail_format = ‘H’;
$mail_copies = ‘’;
$mail_tp_copies = ‘’;
$mail_port = ‘465’;
$mail_tp_connection = ‘S’;

sc_mail_send($mail_smtp_server,
$mail_smtp_user,
$mail_smtp_pass,
$mail_from,
$mail_to,
$mail_subject,
$mail_message,
$mail_format,
$mail_copies,
$mail_tp_copies,
$mail_port,
$mail_tp_connection);

1 Like