bigup401 has asked for the wisdom of the Perl Monks concerning the following question: 



Am trying to use DISTINCT clause  to get all ages for users named john
+, but dont know why am getting single resluts


|      TABLE   USERS         |
+--------+-------------------+
| ID     | NAMES    |  AGE   |
+--------+----------|--------+
|      1 | John     |     35 |
|      2 | Mike     |     31 |
|      3 | John     |     56 |
+--------+-------------------+

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


Am only getting Age 35 for john, 

but i want to get all ages for users named john

[download]

Replies are listed 'Best First'.
Re: fetch all data
by hdb (Monsignor) on Mar 17, 2019 at 15:11 UTC

    fetchrow fetches only one row. If you call it repeatedly yo will ger the other results as well.

[reply]
Re: fetch all data
by stevieb (Canon) on Mar 17, 2019 at 15:13 UTC

    I'm pretty sure you'll want to use something like fetchall_arrayref() (or one of the other "fetchall" methods):

    my $records = $get_age->fetchall_arrayref;

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

    [download]

    In other words, the fetchrow methods literally only grab a single row.

    Update: modified code per poj's post here. I apologize for forgetting to say my code was untested.

[reply]
[d/l]
[select]

      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]
[reply]
[d/l]
        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]

Back to Seekers of Perl Wisdom