CODEWITHSUNDEEP
perldbi
mich kent
mich kent

Reputation: 63

select multiple rows using MySQL in Perl

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

Upvotes: 0

Views: 1103

Answers (2)

Dave Cross
Dave Cross

Reputation: 69274

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;

Upvotes: 2

shoorick
shoorick

Reputation: 129

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";
}

Upvotes: 1

Related Questions