The Perl DBI API and The PHP API

Perl Script Characteristics

Perl scripts are just text files, so you can create them using any text editor. All Perl scripts in this chapter follow the UNIX convention that they begin with a #! (shebang) line that specifies the pathname of the program to use for executing the script. The line I use is as follows:

#! /usr/bin/perl -w

On UNIX, you'll need to modify the #! line if the pathname to Perl is different on your system, such as /usr/local/bin/perl5 or /opt/bin/perl. Otherwise, Perl scripts won't run properly on your system.

I include a space after the #! because some systems interpret the sequence "#! /" as a 4-byte magic number, ignore the line if the space is missing, and thus treat the script as a shell script rather than as a Perl script.

The -w option tells Perl to issue a warning if it finds that you use questionable language constructs or perform operations such as printing uninitialized variables. This is useful because it can alert you to code that should be rewritten.

You can invoke a Perl script myscript.pl as follows on any system to run it:

% perl -w myscript.pl

However, you may also be able to execute the script without naming the perl program explicitly. On UNIX, do this by changing the file mode with chmod to make the script executable:

% chmod +x myscript.pl

Then you can run the script just by typing its name:

% ./myscript.pl

That is the invocation style that will be used for scripts written in this chapter. The leading ./ should be used if the script is located in your current directory (".") and your shell does not have the current directory in its search path. Otherwise, you can omit the "./" from the command name:

% myscript.pl

Under Windows NT-based systems (NT, 2000, and XP), you can set up a filename association between Perl and filenames ending in .pl. For example, if you install ActiveState Perl, its installation program allows you to set up an association so that filenames ending with .pl are run by Perl. In that case, you can run Perl scripts just by naming them on the command line:

C:\> myscript.pl

Perl DBI Overview This section provides background information for DBI—the information you'll need for writing your own scripts and for understanding scripts written by others. If you're already familiar with DBI, you may want to skip directly to the section "Putting DBI to Work." DBI Data Types In some ways, using the Perl DBI API is similar to using the C client library described in Chapter 6. When you use the C client library, you call functions and access MySQL-related data primarily by means of pointers to structures or arrays. When you use the DBI API, you also call functions and use pointers to structures, except that functions are called methods, pointers are called references, pointer variables are called handles, and the structures that handles point to are called objects. DBI uses several kinds of handles. These tend to be referred to in DBI documentation by the conventional names.The way you use these handles will become clear as we go along. Several conventional names for non-handle variables are used as well,This chapter doesn't actually use every one of these variable names, but it's useful to know them when you read DBI scripts written by other people. Name Meaning $dbh A handle to a database object $sth A handle to a statement (query) object $fh A handle to an open file $h A "generic" handle; the meaning depends on context Name Meaning $rc The return code from operations that return true or false $rv The return value from operations that return an integer $rows The return value from operations that return a row count @ary An array (list) representing a row of values returned by a query A Simple DBI Script Let's start with a simple script, dump_members.pl, that illustrates several standard concepts in DBI programming, such as connecting to and disconnecting from the MySQL server, issuing queries, and retrieving data. This script produces output consisting of the Historical League's member list in tab-delimited format. The format is not so interesting in itself; at this point, it's more important see how to use DBI than to produce pretty output. dump_members.pl looks like this: #! /usr/bin/perl -w # dump_members.pl - dump Historical League's membership list use strict; use DBI; my $dsn = "DBI:mysql:sampdb:cobra.snake.net"; # data source name my $user_name = "sampadm"; # user name my $password = "secret"; # password # connect to database my $dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1, PrintError => 0 }); # issue query my $sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email," . "street, city, state, zip, phone FROM member ORDER BY last_name"); $sth->execute (); # read results of query, then clean up while (my @ary = $sth->fetchrow_array ()) { print join ("\t", @ary), "\n"; } $sth->finish (); $dbh->disconnect (); exit (0); PHP Overview The basic function of PHP is to interpret a script to produce a Web page that is sent to a client. The script typically contains a mix of HTML and executable code. The HTML is sent literally to the client, whereas the PHP code is executed and replaced by whatever output it produces. Consequently, the client never sees the code; it sees only the resulting HTML page. PHP scripts developed in this chapter generate pages that are valid as XHTML, not just as HTML. See "Writing Web Output" in "The Perl DBI API," for a brief description of XHTML. When PHP begins reading a file, it simply copies whatever it finds there to the output, under the assumption that the contents of the file represent literal text, such as HTML content. When the PHP interpreter encounters a special opening tag, it switches from HTML mode to PHP code mode and starts interpreting the file as PHP code to be executed. The interpreter switches from code mode back to HTML mode when it sees another special tag that signals the end of the code. This allows you to mix static text (the HTML part) with dynamically generated results (output from the PHP code part) to produce a page that varies depending on the circumstances under which it is called. For example, you might use a PHP script to process the result of a form into which a user has entered parameters for a database search. Depending on what the user types, the search parameters may be different each time the form is submitted, so when the script searches for and displays the information the user requested, each resulting page will be different. Putting PHP to Work The remaining part of this chapter tackles the goals set out in that we have yet to accomplish: For the grade-keeping project, we need to write a script that allows us to enter and edit test and quiz scores. For visitors to the Historical League Web site, we want to develop an online quiz about U.S. presidents and to make it interactive so that the questions can be generated on-the-fly. We also want to allow Historical League members to edit their directory entries online. This will keep the information up to date and reduce the amount of entry editing that must be done by the League secretary.