in reply to Re^6: Selecting DB
in thread Selecting DB

You're just not quite doing enough de-referencing... 

#!/usr/bin/perl

use strict; # https://perlmonks.org/?node_id=11153321
use warnings;
use DBI;
$SIG{__WARN__} = sub { die @_ };

my $dbname = '/tmp/d.11153321';
unlink $dbname;
my $dbh = DBI->connect_cached(
  "DBI:SQLite(RaiseError=>1,PrintError=>0):$dbname");
while()
  {
  my ($table) = split ' ', <DATA> // last;
  <DATA>;
  my (@fields) = split ' ', <DATA>;
  my $fieldnames = join ', ', map "$_ text", @fields;
  my $fields = join ', ', @fields;
  $dbh->do("create table $table ($fieldnames)");
  while( <DATA> )
    {
    /\S/ or last;
    my $values = join ', ', map "'$_'", split;
    $dbh->do("insert into $table ($fields) values ($values)");
    }
  }
system "sqlite3 $dbname .dump";

######################################################################
+#######

my $AID = "XD4555";

my $query = $dbh->prepare("SELECT
snd.EMAIL FROM
FUSERS as m
JOIN USERS as snd ON snd.USERID = m.USERID WHERE (m.USERID_IM = ?)");
$query->execute($AID);
my $user1 = $query->fetchall_arrayref();

my $query_other = $dbh->prepare("SELECT
snd.EMAIL FROM
FUSERS as m
JOIN USERS as snd ON snd.USERID = m.USERID_IM WHERE (m.USERID = ?)");
$query_other->execute($AID);
my $user2 = $query_other->fetchall_arrayref();

use Data::Dump 'dd'; dd {user1 => $user1, user2 => $user2};

my $TotalEmails = [ @$user1, @$user2 ];

use Data::Dump 'dd'; dd {TotalEmails => $TotalEmails};

for my $em ( @$TotalEmails )
  {
  my $rez = join ',', @$em;
  print "$rez\n";
  }

#my $TotalEmails = $user1 + $user2;
#
#foreach my $em ( $TotalEmails ) {
#    my $rez = join( ",", $em);
#    print "$rez\n";
#    }

__DATA__
USERS

ID USERID EMAIL
1  XD4555 JOHNE@DEO.COM
2  JJKKKK JANE@DEO.COM
3  JJKKKK JANE21@DEO.COM

FUSERS

ID USERID USERID_IM
1  XD4555 JJKKKK
2  JJKKKK TYYUPPO

[download]

Outputs: 

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE USERS (ID text, USERID text, EMAIL text);
INSERT INTO USERS VALUES('1','XD4555','JOHNE@DEO.COM');
INSERT INTO USERS VALUES('2','JJKKKK','JANE@DEO.COM');
INSERT INTO USERS VALUES('3','JJKKKK','JANE21@DEO.COM');
CREATE TABLE FUSERS (ID text, USERID text, USERID_IM text);
INSERT INTO FUSERS VALUES('1','XD4555','JJKKKK');
INSERT INTO FUSERS VALUES('2','JJKKKK','TYYUPPO');
COMMIT;
{ user1 => [], user2 => [["JANE21\@DEO.COM"], ["JANE\@DEO.COM"]] }
{ TotalEmails => [["JANE21\@DEO.COM"], ["JANE\@DEO.COM"]] }
JANE21@DEO.COM
JANE@DEO.COM

[download]

The Data::Dump output is there to see what the returned data really is. You could also use Data::Dumper, but I just prefer the ease of Data::Dump.

Replies are listed 'Best First'.
Re^8: Selecting DB
by frank1 (Monk) on Jul 10, 2023 at 12:22 UTC

    i have tried all options, which all monks given me. but am still facing some problem

    this is the script, still cant send to all emails, it only sends to last email. but first ones are not being sent to

    my $AID = "XD4555";

my $query = $dbh->prepare("SELECT
snd.EMAIL FROM
FUSERS as m
JOIN USERS as snd ON snd.USERID = m.USERID WHERE (m.USERID_IM = ?)");
$query->execute($AID);
my $user1 = $query->fetchall_arrayref();

my $query_other = $dbh->prepare("SELECT
snd.EMAIL FROM
FUSERS as m
JOIN USERS as snd ON snd.USERID = m.USERID_IM WHERE (m.USERID = ?)");
$query_other->execute($AID);
my $user2 = $query_other->fetchall_arrayref();


my $TotalEmails = [ @$user1, @$user2 ];

for my $em ( @$TotalEmails ){}

my $message_s = "<p>hi test</p>";

my $smtpserver = 'mail.xxxxxx.com';
my $smtpport = 587;
my $smtpuser   = 'xxxx.com';
my $smtppassword = 'xxxxxxxx';

my $transport = Email::Sender::Transport::SMTP->new({
  host => $smtpserver,
  ssl  => 'starttls',
  port => $smtpport,
  sasl_username => $smtpuser,
  sasl_password => $smtppassword,
});

my $email_s = Email::Simple->create(
  header => [
    To      => join(", ", @$em),
    From    => 'xxxx@xxx.com',
    Subject => "test em",
    'Content-Type' => 'text/html', 
  ],
  body => $message_s,
);

sendmail($email_s, { transport => $transport });

    [download]

    but now the error is that. 'no recipients' after doing some changes myself

[reply]
[d/l]

      You have set the addresses you will be sending to like this:

          To      => join(", ", @$em),

      [download]

      but nowhere have you set $em to anything, so the array is empty, so you have no recipients. A quick SSCCE to show how to pull these AoAs together:

      #!/usr/bin/env perl
use strict;
use warnings;

my $user1 = [ [ 'alpha@foo.com', 'beta@foo.dom' ], [ 'gamma@foo.com' ]
+ ];
my $user2 = [ [ 'delta@foo.com' ], [ 'epsilon@foo.dom', 'zeta@foo.com'
+ ] ];

my $TotalEmails = [ @$user1, @$user2 ];

my @to;
for my $em ( @$TotalEmails ) {
    push @to, @$em;
}

my $tostring = join ',', @to;
print "Full list of addrs: $tostring\n";

      [download]

      See how that works? You don't even really need the intermediate $TotalEmails but I've left it in for clarity. You can now use $tostring when sending your emails and they should all go out.

      🦛

[reply]
[d/l]
[select]
        my $tostring = join ', ', map @$_, @$TotalEmails;

        [download]

        Too golfy ?

[reply]
[d/l]

        Thanks its working. delivering now to all emails

        Thanks much

[reply]
Re^8: Selecting DB
by frank1 (Monk) on Jul 10, 2023 at 09:27 UTC

    Thanks

[reply]

In Section Seekers of Perl Wisdom