in reply to Re^2: output results from sql join
in thread output results from sql join

bugup401:

Yeah, using placeholders is perfect for avoiding SQL injection attacks. To do so, you just put a question mark in place of the values you need to substitute. Then when you execute the statement, you provide one value for each question mark in your statement:

my $STMT = $DBH->prepare(q{
    SELECT SND.firstname as snd_fname, SND.lastname as snd_lname,
           REC.firstname as rec_fname, REC.lastname as rec_lname
    from MSG as M
    -- We'll use SND as the alias for the sender
    join USERS as SND on M.msg_from = SND.usrid
    -- and REC as the alias for the recipient
    join USERS as REC on M.msg_to   = REC.usrid
    where M.msg_id = ?
});

# Fetch the data for message 5
my $msg_id = 5;

$STMT->execute($msg_id);
while (my $row = $STMT->fetchrow_hashref) {
    # do stuff with the data
}

[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Replies are listed 'Best First'.
Re^4: output results from sql join
by bigup401 (Pilgrim) on Sep 29, 2018 at 10:45 UTC

    am still not getting the info, am getting blank resluts i want to get

    msg from userid(1) names and userid. where people all sent me msg to my userid(2) and have not read them. read is the received column 

    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
  M.userid,
  SND.firstname as snd_FNAME,
  SND.lastname as snd_LNAME
from
  msg as M
  join users as SND on M.userid = SND.userid WHERE M.msg_to = ? NOT IN
+ (SELECT RECEIVED 
                       FROM   msg");
$data->execute($janedeo_id);
$datainfo = $data->fetchall_arrayref();
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]
      SELECT M.userid,

      userid is a field in table users not in msg. Try

      SELECT SND.userid, SND.firstname, SND.lastname
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL

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

        it has worked now, thanks poj and roboticus

[reply]

In Section Seekers of Perl Wisdom