Re^3: 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.

Comment on Re^3: output results from sql join Download Code

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]
Re^5: output results from sql join by poj (Abbot) on Sep 29, 2018 at 12:03 UTC
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]
Re^6: output results from sql join by bigup401 (Pilgrim) on Sep 29, 2018 at 13:09 UTC
it has worked now, thanks poj and roboticus	[reply]


Welcome to the Monastery
	PerlMonks