am selecting 3 rows from MySQL
but am only getting first one that's - name. i only get name only
$get = $dbh->prepare("select name, age, country from tble where me = ?");
$get->execute('john');
while(my @row = $get->fetchrow_array){
$results = $row[0],$row[1],$row[3];
}
am just getting name only no other rows
MySQL is a red herring here. You're using DBI correctly, but it goes wrong once you've extracted the data from the database.
Your problem is with this line:
$results = $row[0],$row[1],$row[3];
This is very similar to running this code:
$x = 100, 200, 300;
If you then print $x you'll see it contains "100". You have three items on the right-hand side of your assignment operator and only one variable on the left-hand side, so only one of the values (the first one) ends up assigned to the variable.
I'll add here that if you had use warnings turned on in your code (and all good Perl programmers always have use strict and use warnings turned on) then you would have got a warning about "useless use of a constant in void context" which would have indicated that there was something wrong.
There are a couple of approaches you can take to fix this. And which one you choose, depends on what you are trying to do. The obvious solution is to change $results into an array:
@results = ($row[0], $row[1], $row[2]);
Notice I've also put parentheses around your list of values. These are required to tell Perl that this is a list assignment. Of course, this is just a complicated way of writing:
@results = @row;
The other option is to take your three values and turn them into a single string. This isn't a good choice if you're going to use those values individually elsewhere in your code, but if you just want something to print out, you could write something like:
$results = "$row[0],$row[1],$row[2]";
Again, there's a simpler way to write this:
$results = join ',', @row;
Name, age and country aren’t rows — they are columns.
You can use selectcol_arrayref to get one column only:
$name_ref = $dbh->selectcol_arrayref(
q{
SELECT name
FROM tble
WHERE me = ?
},
{},
'john'
);
But if you want to get three columns you should use selectall_arrayref
my $rows = $dbh->selectall_arrayref(
q{
SELECT name, age, country
FROM FROM tble
WHERE me = ?
},
{ Slice => {} },
'john'
);
foreach my $row ( @$rows ) {
print "$row->{'name'} aged $row->{'age'} from $row->{'country'}\n";
}
$results? Note that you are assigning a list to a scalar:$results = $row[0],$row[1],$row[3]which will truncate to the first item of the list, namely$row[0]$results = "$row[0], $row[1], $row[3]";$results = {name => $row[0], age => $row[1], country => $row[3]};to make it a hash reference.my ($name, $age, $country) = @row;Also, there is no$row[3]element.