in reply to output results from sql join

bigup401:

It looks like you're trying to get the sender and recipient user names for messages and are having trouble with the SQL. You can use the same table multiple times with a different alias each time as shown below. I'm aliasing the USERS table to SND for the sender and to REC for the recipient to get the first and last name of both the sender and recipient for each message:

SELECT M.msg_id, 
       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

[download]

If you're going to fetch all the information as arrays, you don't actually need to alias the column names, and you could just print it as you were suggesting. But you need to be careful with your variable names. You're trying to use $data_check in two places, and you haven't loaded $datainfo. I've changed it a little:

$data->execute();
my $data_check = $data->fetchall_arrayref();
for my $row (@$data_check) {
   my ($msgid, $snd_fname, $snd_lname, $rec_fname, $rec_lname) = @$row
+;
   printf "% 6u %-32.32s %-32.32s\n", 
         $msgid, 
         join(" ", $snd_lname, $snd_fname), 
         join(" ", $rec_lname, $rec_fname);
}

[download]

However, fetching all the data at once might be a problem when you're dealing with large datasets. Also, by fetching the data as arrays, then any change to your select statement will require you to change the code that converts each array slot into an individual variable. For that reason, I normally fetch a row at a time and fetch hashrefs instead of arrays, like this:

$data->execute();
my $data_check = $data->fetchall_arrayref();
while (my $row = $data->fetchrow_hashref) {
   printf "% 6u %-32.32s %-32.32s\n", 
         $row->{msgid}, 
         join(" ", $row->{snd_lname}, $row->{snd_fname}), 
         join(" ", $row->{rec_lname}, $row->{rec_fname});
}

[download]

Here, since I've fetched the data as hashes, I can just use the column aliases as the keys for each row. That way, I don't have to map the columns to variables, instead I can just use the aliased column names, and the order doesn't matter at all.

I hope this helps. I couldn't quite tell exactly what you were asking, so I just tossed out my thoughts. I haven't tested any of the code, so if there are any syntax errors, let me know and I'll update it with corrections.

...roboticus

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

Replies are listed 'Best First'.
Re^2: output results from sql join
by bigup401 (Pilgrim) on Sep 28, 2018 at 21:57 UTC

    thanks alot roboticus. and i made some changes according to wat i want

    the problem i have where to put placeholder in mysql statment to be safe from sql injection. second my statement is like this

    $myid = 1;
 select msg_from FROM msg WHERE msg_to = $myid
so am selecting only where msg came from who sent it where all people 
+sent msg to me - my userid 1

msg_from is the id which shows this msg is from this userid from tble 
+users.
so msg_to is my userid the acct owner. so msg comes to me. but i need 
+to see who sent them.

    [download]
    so this is 

msg_from = userid
msg_to   = to me. my userid (1)

so am selecting all people who sent msg to my userid -my userid 1
bt the problem is in the sql statement bt the rest is ok

    [download]

    thanks alot

[reply]
[d/l]
[select]

      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.

[reply]
[d/l]

        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]

In Section Seekers of Perl Wisdom