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

errors. when i try ur example the script dies all. but mine works just only not getting the rest columns is the problem. but works and prints all info very well

Can't use an undefined value as an ARRAY reference at C:/xampp/perl/ve
+ndor/lib/DBI.pm line 2047.

[download]

Replies are listed 'Best First'.
Re^5: sql join with multiple select
by poj (Abbot) on Oct 04, 2018 at 14:28 UTC
    mine works just only not getting the rest columns is the problem

    Ok, copy/paste the code you have that works here

    poj
[reply]

      this works. simple and easy

      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]

      the problem is not the code. the problem is at sql statement

      coz i have made some tests and found the problem is in sql statement

      this statement works

      my $data = $DBH->prepare("SELECT SND.userid, SND.fname, SND.lname
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL");

<p>but my problem and question was after adding some column to table m
+sg and printing them out. i get error that tells me cant find such da
+ta from db according to my script. that means cant locate any data in
+ db </p>
<p>my problem is here wen i include other columns to be selected. the 
+statement dies and cant print anything just getting error nothing to 
+print from db</p>
<code>
my $data = $DBH->prepare("SELECT SND.userid, SND.fname, SND.lname, SND
+.city, SND.age, SND.city 
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL");

      [download]
[reply]
[d/l]

        In your original post the MSG table with added fields was

        msg_id    msg_from     msg_to      received  title    age     country

        but now your sql select includes city twice. What columns are you trying to add. ? 

        SELECT SND.userid, SND.fname, SND.lname, SND.city, SND.age, SND.city
        poj
[reply]
Re^5: sql join with multiple select
by marto (Cardinal) on Oct 04, 2018 at 14:32 UTC
[reply]

      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]

In Section Seekers of Perl Wisdom