Re^6: sql join with multiple select

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]

Comment on Re^6: sql join with multiple select Download Code

Replies are listed 'Best First'.
Re^7: sql join with multiple select by poj (Abbot) on Oct 04, 2018 at 15:22 UTC
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]
Re^8: sql join with multiple select by bigup401 (Pilgrim) on Oct 04, 2018 at 15:27 UTC
ya its userid, snd.fname, snd.lname but that error ur getting Not an ARRAY reference i dont get it here for sure	[reply]
Re^9: sql join with multiple select by AnomalousMonk (Archbishop) on Oct 04, 2018 at 17:01 UTC
but that error ur getting Not an ARRAY reference i dont get it here for sure Then the code you're posting is not the code you're running. And the carousel begins another round. Give a man a fish: `<%-{-{-{-<`	[reply] [d/l]