fetch all data

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]

Comment on fetch all data Download Code

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]
Re^2: fetch all data by bigup401 (Pilgrim) on Mar 17, 2019 at 15:54 UTC
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]
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]
Re^4: fetch all data by stevieb (Canon) on Mar 17, 2019 at 16:24 UTC
Re^4: fetch all data by bigup401 (Pilgrim) on Mar 17, 2019 at 18:04 UTC
Re^5: fetch all data by poj (Abbot) on Mar 17, 2019 at 18:23 UTC
Some notes below your chosen depth have not been shown here


good chemistry is complicated, and a little bit messy -LW
	PerlMonks