Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

MySQL count with DBI

by aenima (Initiate)
on May 09, 2003 at 12:02 UTC ( #256832=perlquestion: print w/replies, xml ) Need Help??

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://256832]
Approved by smitz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2022-10-04 00:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (15 votes). Check out past polls.

    Notices?