Grokbase › Groups › Perl › dbi-users › May 2003
FAQ
Just installed/tested MySQL - works great. Now working the DBI to manipulate a db. I want to get a count of rows in a table.

In MySQL, it's:

SELECT COUNT(*) FROM MYTABLE;

In DBI I've tried several different things with no success. The FAQ appears to be down. My books Programming the Perl DBI and MySQL and msql appear to have great examples for advanced concepts, but not for the most primitive examples like this one. The online DBI docs state (under the section for the rows method):

One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that.

But I guess I just haven't had enough coffee. My snippet:

my $sth = $dbh->prepare( "SELECT COUNT(*) FROM " . $tablename );
$sth->execute();
my $rowscount = $sth->rows;

There are 2 recs in the table, but $rowscount contains 1.

Would appreciate a clue, TIA,
Glen

Search Discussions

  • Michael Nhan at May 13, 2003 at 3:32 pm ⇧
    Hi,

    You missed the previous paragraph...
    Per DBI doc:

    <quote>
    For SELECT statements, it is generally not possible to
    know how many rows will be returned except by fetching
    them all. Some drivers will return the number of rows
    the application has fetched so far, but others may
    return -1 until all rows have been fetched. So use of
    the rows method or $DBI::rows with SELECT statements is
    not recommended.
    </quote>

    Mike
    Date: Tue, 13 May 2003 11:22:40 -0400
    From: "Moulder, Glen" <Moulder@wpb.nuwc.navy.mil>
    To: dbi-users@perl.org
    Subject: Q: return count of rows (newbie)?

    Just installed/tested MySQL - works great. Now working the DBI to manipulate a db. I want to get a count of rows in a table.

    In MySQL, it's:

    SELECT COUNT(*) FROM MYTABLE;

    In DBI I've tried several different things with no success. The FAQ appears to be down. My books Programming the Perl DBI and MySQL and msql appear to have great examples for advanced concepts, but not for the most primitive examples like this one. The online DBI docs state (under the section for the rows method):

    One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that.

    But I guess I just haven't had enough coffee. My snippet:

    my $sth = $dbh->prepare( "SELECT COUNT(*) FROM " . $tablename );
    $sth->execute();
    my $rowscount = $sth->rows;

    There are 2 recs in the table, but $rowscount contains 1.

    Would appreciate a clue, TIA,
    Glen
    ---//---
    I don't need to compromise my principles, because they don't have the
    slightest bearing on what happens to me anyway.
    ---Calvin (Calvin & Hobbes).


    A computer lets you make more mistakes faster than any invention in human
    history - with the possible exceptions of handguns and tequila.
    ----Ratliffe, M.
  • Marco Guazzone at May 13, 2003 at 3:34 pm ⇧

    On Tue, 13 May 2003, Moulder, Glen wrote:

    Just installed/tested MySQL - works great. Now working the DBI to manipulate a db. I want to get a count of rows in a table.

    In MySQL, it's:

    SELECT COUNT(*) FROM MYTABLE;

    In DBI I've tried several different things with no success. The FAQ appears to be down. My books Programming the Perl DBI and MySQL and msql appear to have great examples for advanced concepts, but not for the most primitive examples like this one. The online DBI docs state (under the section for the rows method):

    One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that.

    But I guess I just haven't had enough coffee. My snippet:

    my $sth = $dbh->prepare( "SELECT COUNT(*) FROM " . $tablename );
    $sth->execute();
    my $rowscount = $sth->rows;

    There are 2 recs in the table, but $rowscount contains 1.

    Would appreciate a clue, TIA,
    Glen

    you can do:

    my $sth = $dbh->prepare( "SELECT COUNT(*) FROM " . $tablename );
    $sth->execute();
    my ( $count ) = $sth->fetchrow_array();

    or simply:

    my ( $count ) = $dbh->selectrow_array( "SELECT COUNT(*) FROM" . $tablename );

    Regards,

    Marco Guazzone
  • Jeffrey W. Baker at May 13, 2003 at 3:35 pm ⇧

    On Tue, 2003-05-13 at 08:22, Moulder, Glen wrote:
    Just installed/tested MySQL - works great. Now working the DBI to manipulate a db. I want to get a count of rows in a table.

    In MySQL, it's:

    SELECT COUNT(*) FROM MYTABLE;

    In DBI I've tried several different things with no success. The FAQ appears to be down. My books Programming the Perl DBI and MySQL and msql appear to have great examples for advanced concepts, but not for the most primitive examples like this one. The online DBI docs state (under the section for the rows method):

    One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that.

    But I guess I just haven't had enough coffee. My snippet:

    my $sth = $dbh->prepare( "SELECT COUNT(*) FROM " . $tablename );
    $sth->execute();
    my $rowscount = $sth->rows;

    There are 2 recs in the table, but $rowscount contains 1.
    That's because your query returns this table:

    2

    Which only has one row (and only one column, too.)

    -jwb

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedMay 13, '03 at 3:23p
activeMay 13, '03 at 3:35p
posts4
users4
websitedbi.perl.org

4 users in discussion

Marco Guazzone: 1 post Jeffrey W. Baker: 1 post Michael Nhan: 1 post Moulder, Glen: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase