There are several ways (besides fetchrow_array()) to retrieve results from a query. Which others have you tried using, and what happened?

A suggestion: For a number of reasons that you'll find covered if you search for "bind", it's a good idea to use parameter binding in queries. This looks like:

  my $sql = <<SQL;
    SELECT count(*)
    FROM files
    WHERE geo=? AND lob="is" AND country=?
  SQL
  my $sth = $dbh->prepare($sql);
  $sth->execute($geo, $country);
  my($count) = $sth->fetchrow_array();
  $sth->finish();
[download]

(Edited to correct a typo in the code.)

(Eeek. Edited again to correct a worse typo.)

I thought I knew of two separate ways for retriving data from a query..

1.Use bind, for example:

$query_is->bind_columns(\$count_is);
[download]

2.Use fetchrow_array(), for example:

@row = $query_is->fetchrow_array
[download]

.. could you (or someone else) show me how to use "bind" regarding the example in my first posting.

When I do bind (same code as in my original posting) but without the "fetchrow_array" line it does not output any data? i.e it only outputs data if I do the fetchrow_array command.

Thanks

M

When I do bind (same code as in my original posting) but without the "fetchrow_array" line it does not output any data? i.e it only outputs data if I do the fetchrow_array command.

O.K., I think I see where the misunderstanding is. You're trying to arrange to have the result of the query show up in a variable. There are two ways to do that: you can do the appropriate binding to cause the result of a fetch to get stuffed into a variable, or you can fetch a row (either as an array or as a hash) and extract the value manually. You're trying to do the former; my snippet does the latter.

In both cases, unless a row is fetched, there's no value there to get. Hence you need to force a fetch.

I avoid using bind_column() et al. because I find the resulting code is harder to follow. Readers can easily follow code that manually fetches a row and extracts values, but using column binding forces readers to remember that there's a magic wormhole in that region of space that causes values to automagically appear elsewhere.

my $sql_is = qq{ SELECT count(*) FROM files WHERE (geo="$geo" AND lob=
  +"is" AND country="$key") };
my $count = $dbh->selectrow_array($sql_is);
print "$count\n";
[download]

Update Oops!! I just realized you're looking for a count. I'm not sure if this will work.

Update Above code edited to correct method calls don't interpolate in strings

was:  print "$dbh->selectrow_array($sql_is)\n";
[download]

Thanks! chromatic