http://www.perlmonks.org?node_id=256832

aenima has asked for the wisdom of the Perl Monks concerning the following question:

I have some problems to count the number of post in my MySQL-database with Perl DBI. I understand that the SQL-command is SELECT COUNT(*) FROM music;. Does anybody have and idea?

Replies are listed 'Best First'.
Re: MySQL count with DBI
by smitz (Chaplain) on May 09, 2003 at 12:12 UTC
    Yeah I HAD an idea, a combination Hat / Shoe garment, but the backers dropped out and I
    lost EVERYTHING. Anyway, does this answer your non-question?
    #!perl -w use strict; use DBI; my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_pas +sword') or die "Couldn't open database: $DBI::errstr; stopped"; # Prepare the SQL query for execution my $sth = $dbh->prepare(<<SQL) or die "Couldn't prepare statement: $DB +I::errstr; stopped"; SELECT COUNT(*) FROM music SQL # Execute the query $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop +ped"; #print result my ($count) = $sth->fetchrow_array(); print "Found $count results\n";
    Smitz

    Yeah I copied and pasted from stephens excellent Reading from a database. Sorry, stephen

      Not to be a pita, but I think it is vastly clearer and safer to explicitly write SQLs explicitly into a variable, rather than read them from STDIN. In addition, I'm not a huge fan of using ->fetchrow_array() to get back a single method: that is exactly what ->selectrow_array() is meant for, I think. Finally, if the questioner is just starting to use DBI he should probably enable it's error-handling apparatus explicitly until he knows when/why he might not want to use it. Similarly, I would explicitly turn off autocommit to limit accidental damage to the DB.

      No major changes there, smitz, but just seems "safer" this way to me....

      my $dbh = DBI->connect('DBI:mysql:my_db', 'user', 'pass', {RaiseError +=> 1, PrintError => 1, AutoCommit => 0}) or die "Error connecting to +DB: $DBI::errstr\n"; my $sql = "SELECT COUNT(*) FROM music"; my @row = $dbh->selectrow_array($sql); print "Found $row[0] results\n";
      -Tats
        Yeah you are right, I should have been more discriminating when I copied stephen's tut, however, Im not sure why you say:
        > it is vastly clearer and safer to explicitly write SQLs explicitly into a variable, rather than read them from STDIN.
        Where is my snippet doing that?

        Smitz