Having an SQL SELECT query, how do I get number of items?

I'm writing a web app in Perl using Dancer framework. The database is in sqlite and I use DBI for database interaction.

I'm fine with select statements, but I wonder is there a way to count selected rows.

E.g. I have

get '/' => sub {
    my $content = database->prepare(sprintf("SELECT * FROM content LIMIT %d",
            $CONTNUM));
    $content->execute;
    print(Dumper($content->fetchall_arrayref));
};

How do I count all items in the res开发者_如何学运维ult without issuing another query?

What I want to achieve this way is showing 30 items per page and knowing how many pages there would be. Of course I can run SELECT COUNT (*) foo bar, but it looks wrong and redundant to me. I'm looking for a more or less general, DRY and not too heavy on database way to do so.

Any SQL or Perl hack or a hint what should I read about would be appreciated.

// I know using string concatenation for querys is bad

You have to do it the hard way: one query to get the count and another to get your desired slice of the row set:

my $count = $database->prepare('SELECT COUNT(*) FROM content');
$count->execute();
my $n = $count->fetchall_arrayref()->[0][0];

my $content = $database->prepare('SELECT * FROM content LIMIT ?');
$content->execute($CONTNUM);
#...

Not too familiar with perl, but I assume you can just store the result of $content->fetchall_arrayref and retrieve the count from that array befor you print it.

[edit]

Something like

my $ref = $content->fetchall_arrayref;
my $count = scalar(@$ref);

Don't use sqlite myself but the following might work:

select * from table join (select count(*) from table);

Whether the above works or not the first thing I'd look for is scrollable cursors if you are going to page through results - I doubt sqlite has those. However, in DBI you can use fetchall_arrayref with a max_rows to fetch a "page" at a time. Just look up the example in the DBI docs under fetchall_arrayref - it is something like this:

my $rowcache = [];
while( my $row = ( shift(@$rowcache) || shift(@{$rowcache=$sth->fetchall_arrayref(undef,100)||[]}) )
         ) {
           # do something here
         }

UPDATE: Added what you'd get with selectall_hashref assuming the table is called content with one integer column called "a":

$ perl -le 'use DBI; my $h = DBI->connect("dbi:SQLite:dbname=fred.db"); my $r = $h->selectall_hashref(q/select * from content join (select count(*) as count from content)/, "a");use Data::Dumper;print Dumper($r);'
$VAR1 = {
          '1' => {
                   'count' => '3',
                   'a' => '1'
                 },
          '3' => {
                   'count' => '3',
                   'a' => '3'
                 },
          '2' => {
                   'count' => '3',
                   'a' => '2'
                 }
        };

If you want to know how many results there will be, as well as getting the results themselves, all in one query, then get the count as a new value:

SELECT COUNT(*) AS num_rows, * from Table WHERE ...

Now the row count will be the first column of every row of your resultset, so simply pop that off before presenting the data.