Counting Your Matches

We've seen a couple different ways to select data. What if we don't want all of that data but instead want just the number of items that match? SQL has a COUNT function for this purpose.

01: #!/usr/bin/perl -w 02: # 03: # select4.pl 04: # Chapter 4 05: # Listing 4-4 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr");

Lines 1-10 are again very similar to the other program listings in this chapter.

11: my $sql = qq(SELECT COUNT(*) FROM library);

Line 11 is our SQL statement that uses the COUNT function. This statement tells the DBMS to count all of the records that contain data in any field in the library table. The * is a wildcard character and stands for all of the fields in this table-so if any of the fields in a record contain data, then it is counted as a match.

12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my $record = $sth->fetch){

Lines 12-14 are identical to our previous examples.

15:     print "You have $record->[0] books."; 16:     print "\n"; 17: } 

Line 15, however, is a bit different. We could have left it the same as the corresponding line in the other programs, but attempting to loop through an array with only a single item in it would be a bit wasteful. Instead, we still use the $record variable as a pointer to the array but we want only element 0 (the first item in the array) because we know that there is going to be only one element returned to us. In this case, we use the arrow operator, a.k.a. the dereferencing operator. Remember, the variable $record is a reference to an array. By using $record->[0], we dereference the array to get element 0. Another way to write this is ${$record}[0], but that is much harder to read than a simple $record->[0].

Recall that since $record holds an arrayref, we access its first element with $record->[0], not $record[0]-since $record->[0] means "element 0 in the array that $record points to," whereas $record[0] means "element 0 in the array @record." So if you get warnings from Perl that you're trying to access an undeclared variable @record, it's probably because you said something $record[0] when you meant $record->[0].

As you can see in the following code, there isn't much of an output for this program, but it does exactly what we've programmed it to do.

You have 60 books.