Creating a Phone Number Lookup Program

Getting information into a database is a good thing, but the point of databases is to make the data easily available. The next program is the sort in which Perl excels. The program is intended to find phone numbers quickly. It runs from the command-line but can be run from macros. It looks like this:

$ phone phone: please supply a name! usage:      ./phone name example:    ./phone Smith  $ phone chris Chris Chalmers (Chris)         home: [phone]         mobile: [phone] $

And here’s the code:

01: #!/usr/bin/perl –w 02: #  03: # program 14-2, Chapter 14, Listing 2 04: #      05: use DBI; 06: use strict;

Lines 1–6 start in the usual way: warnings, strict, and the DBI module.

07: # check the user gave a search term 08: my $name = shift(@ARGV); 09: unless ($name) { 10:     die "$0: please supply a name! 11: usage:      $0 name 12: example:    $0 Smith\n"; 13: }

Line 7 is simply a comment.

Line 8 uses the shift function to get a value from the @ARGV array. The @ARGV array is populated with the values that the user passed on the command line.

Line 9 checks to see if $name contains any data. If not, then this block of code is entered.

Lines 10–12 use the die function to print out a message to the user and program usage instructions. This causes the program to stop right here because it cannot go on unless it has data to search for.

Line 13 ends the unless block which began on line 9.

14: # connect to DBMS and get a database handle 15: my $dsn = ‘DBI:mysql:BibleBook’; 16: my $connection = DBI->connect($dsn, ‘bookuser’, ‘testpass’) 17:     or die "Can’t connect to database: $dsn\nError: $DBI::errstr\nExiting";

Line 14 is a comment.

Line 15 creates a string named $dsn that is our data source name.

Line 16 creates a connection to the database and stores the result in the $connection string.

Line 17 calls the die function and prints out an error message telling the user that there was a problem connecting to the database.

This time the data source name string is in a separate variable so it can be reused in the error message. A school of thought says you shouldn’t give out information unnecessarily—for example your database names—but since users can read the Perl source to see database and usernames and passwords, it seems harmless in this case. It would be different if this were used as a CGI program.

18: $name =~ /(\w+)/; 19: $name = $connection->quote("%$1%");

Lines 18 and 19 do some simple validation on the name. Because we don’t entirely trust our users, we select only the first string of “word” characters; then we add SQL wildcard characters to it so that matches don’t have to be complete. The DBI quote function properly quotes the resulting string.

20: # prepare the statement and get a statement handle 21: my $sql = <<EOS; 22: SELECT first_name,last_name,greeting,phone_type,phone_number,note 23: FROM person, phone 24: WHERE person.id = phone.id 25: AND (first_name   LIKE $name 26:      OR last_name LIKE $name 27:      OR greeting  LIKE $name) 28: EOS

Line 20 is a comment.

Line 21 begins a here-document that uses EOS as it’s terminator. This means that anything between this line, and the EOS on line 28 is treated as a string and will be stored in the $sql variable.

Lines 22–27 define the SELECT statement. The statement joins the person and phone tables on their common column and looks for the entered name in any of the name fields. Because the statement is used only once and the name has been quoted, $name is plugged straight into the SQL.

Line 28 ends the here-document that began on line 21.

29: my $query = $connection->prepare($sql) 30:     or die "$0: can’t prepare ‘$sql’\nError: $DBI::errstr\nExiting";      31: # run the query 32: $query->execute();

Lines 29–30 prepare the query and call the die method if there’s a problem.

Line 31 is a comment.

Line 32 executes the query. No parameters are necessary.

33: # get/display the results 34: my $current_name = ‘’;

Line 33 is a comment.

Line 34 defines the variable used to keep track of when the name changes, and initially sets it to an empty string.

35: while (my @data = $query->fetchrow()) {

Line 35 begins a while loop that is the core of this program. This while loop will continue as long as fetchrow returns more data.

36:     if ($current_name ne "$data[0] $data[1]") { 37:         print "$data[0] $data[1] ($data[2])\n"; 38:         $current_name = "$data[0] $data[1]"; 39:     }

Line 36 checks to see if the value in $current_name is not equal to the values in "$data[0] $data[1]". If not, then the name has changed so we need to print out the new contact name. If the name is the same, we don’t print out the name again, instead we move on and print out the next phone number.

Line 37 prints out the current contact’s name and greeting.

Line 38 stores the first name ($data[0]) and last name ($data[1]) into the $current_name string.

Line 39 ends the if block.

40:     print "\t$data[3]: $data[4]\t$data[5]\n"; 41: }

Line 40 prints the phone data.

Line 41 ends the while loop that began on line 35.

42: # tidy up 43: $query->finish(); 44: $connection->disconnect(); 45: exit 0;

Line 42 is a comment.

Lines 43–45 release any resources the statement and database handles hold before exiting.

This was a simple program with no subroutines and one single purpose.