in reply to Re: fetch all data
in thread fetch all data

the results comes null. i get nothing 

my $get_age = $DBH->prepare("SELECT DISTINCT AGE FROM USERS WHERE NAME
+S = ?");
$get_age->execute('John');

my $ages = $get_age->fetchall_arrayref;

for my $age (@{ $ages }){
print $age;
}

[download]

Replies are listed 'Best First'.
Re^3: fetch all data
by poj (Abbot) on Mar 17, 2019 at 16:22 UTC
    fetchall_arrayref returns a reference to an Array of Arrays (AOA).

    my $sth = $DBH->prepare('
   SELECT DISTINCT AGE 
   FROM USERS 
   WHERE NAMES = ?');
$sth->execute('John');

my $records = $sth->fetchall_arrayref;

for my $record (@$records){
  printf "%d\n", $record->[0]; # AGE field
}

    [download]

    If that doesn't work try the same code without the WHERE statement.

    my $sth = $DBH->prepare('
   SELECT DISTINCT AGE 
   FROM USER');
$sth->execute();

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

      Good catch. It's been quite some time since I've dabbled with DB stuff. I should have made it clear that my code was untested.

[reply]
      i tried that earlier, but i think it has been my problem to not diagno
+se the problem. until i have added 
print "Content-type: text/html\n\n" and print to rest to the top of sc
+ript and am getting the results the actual results i want in header. 
+but not in html. in html am just getting only  1 results

      [download]
      results

35 56 Content-type: text/html # this comes very well but. cant use it 
+on top of script for production use 

Age list

35 # am getting only this in html and i want to get all like in header

      [download]
      #!/usr/bin/perl -w
print "Content-type: text/html\n\n";

use DBI;

my $host =   "localhost";
my $usr =    "root";
my $pwd =    "";
my $dbname = "test";

my $DBH = DBI->connect("DBI:mysql:$dbname:$host", $usr, $pwd, {
                                  AutoCommit => 0,
                                  RaiseError => 1, 
                                  }) or die $DBI::errstr;


my $sth = $DBH->prepare('
   SELECT DISTINCT AGE 
   FROM USERS 
   WHERE NAMES = ?');
$sth->execute('John');

my $records = $sth->fetchall_arrayref;

for my $record (@$records){

  print $rest = $record->[0]; 
}

print "Content-type: text/html\n\n";
print <<START_HTML;

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body>
<p>Age list </p>
<p>$rest </p>
</body>

</html>

START_HTML

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

        Don't print anything before the header. Create the list and then print it in the page

        my $records = $sth->fetchall_arrayref;
my $list = join ' ',map{$_->[0]}@$records;

print "Content-type: text/html\n\n";
print <<END_HTML;

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body>
<p>Age list </p>
<p>$list</p>
</body>
</html>

END_HTML

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

In Section Seekers of Perl Wisdom