in reply to Re^4: sql join with multiple select
in thread sql join with multiple select

going round in circles...

Replies are listed 'Best First'.
Re^6: sql join with multiple select
by bigup401 (Pilgrim) on Oct 04, 2018 at 14:49 UTC

    this works

    as i told. the problem is when i added some column to table. so cant select other added column from table msg

    TABLE users

userid FNAME LNAME 
1      JOHN  DEO
2      JANE  DEO

TABLE MSG

msg_id    msg_from     msg_to      received  
1         userid(1)    userid(2)   null       


$janedeo_id = 2;


my $data = $DBH->prepare("SELECT SND.userid, SND.firstname, SND.lastna
+me
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received IS NULL");
$data->execute($janedeo_id);
while (my $row = $data->fetchrow_hashref) {
    foreach $row ( @$data) {
        ($userid, $snd_FNAME, $snd_LNAME) = @$data;
}
}
my $templ    = <<START_HTML;
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1
+" />
<title>Untitled Document</title>
</head>

<body>
[% FOREACH name IN list %]
<p>userid    [% name.0 %] </p>
<p>firstname [% name.1 %] </p>
<p>lastname  [% name.2 %] </p>
[% END %]
</body>
</html>

START_HTML

$template->process (\$templ, { list => \@$data })
        or die $template->error;

    [download]
[reply]
[d/l]

      If your user table is this

      TABLE users

userid FNAME LNAME 
1      JOHN  DEO
2      JANE  DEO

      [download]

      this line

      my $data = $DBH->prepare("SELECT SND.userid, SND.firstname, SND.lastname

      give the error

      DBD::mysql::st execute failed: Unknown column 'SND.firstname' in 'field list'

      If I correct that error, the next error is 

      Not an ARRAY reference
      at this line

          foreach $row ( @$data) {

      because as I told you here $data is a statement handle not an array refence

      I don't know what code you have that is working but it's not what you posted.

      poj
[reply]
[d/l]
[select]

        ya its userid, snd.fname, snd.lname

        but that error ur getting Not an ARRAY reference

        i dont get it here for sure

[reply]

In Section Seekers of Perl Wisdom