in reply to sql join with multiple select

this works
my $data = $DBH->prepare(…)
'
'
$template->process (\$templ, { list => \@$data })
         or die $template->error;

How can it ? I get the error "Not an ARRAY reference"
because $data is a statement handle. Your array ref is $datainfo

poj

Replies are listed 'Best First'.
Re^2: sql join with multiple select
by LanX (Saint) on Oct 03, 2018 at 21:18 UTC
    for me this looks like the mix of two different codesnippets.

    (I'm getting a bit worried, if programs already start to have sex now...)

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

[reply]
Re^2: sql join with multiple select
by bigup401 (Pilgrim) on Oct 04, 2018 at 07:59 UTC

    its typing error. was tired. but its like this 

    $data->execute($janedeo_id);
$datainfo = $data->fetchall_arrayref();
while (my $row = $data->fetchrow_hashref) {
    foreach $row ( @$data) {
        ($userid, $snd_FNAME, $snd_LNAME) = @$data;
}
}

    [download]
[reply]
[d/l]

      You can avoid typing errors by copy/pasting from your actual code.
      If you want to SELECT more fields then add them into the SELECT part, not the WHERE part. This is explained in the SQL tutorial suggested by LanX. Change this

      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 IN (SELECT title, age, country FROM MSG)

      [download]

      to this

      SELECT userid, FNAME, LNAME, title, age, country
FROM msg as M
JOIN users as SND 
  ON SND.userid = M.msg_from
WHERE M.msg_to = ? 
  AND M.received iS NULL

      [download]

      But, if title, age and country relate to the user why add them to message table MSG and not the user table ?. Also, age increases over time whereas birth date/year is constant. If required, age can be calculated.

      As your requirements are increasing this seems a good time to change your data structure from an ArrayOFArrays(AoA) to an ArrayOfHashes(AoH) by adding {} here my $data = $sth->fetchall_arrayref({}); and changing your template to 

      <body>
[% FOREACH name IN list %]
<p>userid    [% name.userid %] </p>
<p>firstname [% name.FNAME %] </p>
<p>lastname  [% name.LNAME %] </p>
<p>title     [% name.title %] </p>
<p>age       [% name.age %] </p>
<p>country   [% name.country %] </p>
[% END %]
</body>

      [download]

      The advantage is a clearer template and the order of fields in the SELECT does not matter.

      Read more... (1504 Bytes)
      poj
[reply]
[d/l]
[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]
[reply]
[d/l]

In Section Seekers of Perl Wisdom