16.3.1 Problem
You want to run Perl, PHP, or Python programs in a web environment.
16.3.2 Solution
Execute them using the Apache server.
16.3.3 Discussion
This section describes how to configure Apache for running Perl, PHP, and Python scripts, and illustrates how to write web-based scripts in each language.
There are typically several directories under the Apache root directory, which I'll assume here to be /usr/local/apache. These directories include:
bin
Contains httpdthat is, Apache itselfand other Apache-related executable programs
conf
For configuration files, notably httpd.conf, the primary file used by Apache
htdocs
The root of the document tree
logs
For log files
To configure Apache for script execution, edit the httpd.conf file in the conf directory. Typically, executable scripts are identified either by location or by filename suffix. A location can be either language-neutral or language-specific.
Apache configurations often have a cgi-bin directory under the server root directory where you can install scripts that should run as external programs. It's configured using a ScriptAlias directive:
ScriptAlias /cgi-bin/ /usr/local/apache/cgi-bin/
The second argument is the actual location of the script directory in your filesystem, and the first is the pathname in URLs that corresponds to that directory. Thus, the directive just shown associates scripts located in /usr/local/apache/cgi-bin with URLs that have cgi-bin following the hostname. For example, if you install the script myscript.py in the directory /usr/local/apache/cgi-bin on the host apache.snake.net, you'd request it with this URL:
http://apache.snake.net/cgi-bin/myscript.py
When configured this way, the cgi-bin directory can contain scripts written in any language. Because of this, the directory is language-neutral, so Apache needs to be able to figure out which language processor to use to execute each script that is installed there. To provide this information, the first line of the script should begin with #! followed by the pathname to the program that executes the script, and possibly some options. For example, a script that begins with the following line will be run by Perl, and the -w option tells Perl to warn about questionable language constructs:
#! /usr/bin/perl -w
Under Unix, you must also make the script executable (use chmod +x), or it won't run properly. The #! line just shown is appropriate for a system that has Perl installed as /usr/bin/perl. If your Perl interpreter is installed somewhere else, modify the line accordingly. If you're on a Windows machine with Perl installed as D:Perlinperl.exe, the #! line should look like this:
#! D:Perlinperl -w
For Windows users, another option that is simpler is to set up a filename extension association between script names that end with a .pl suffix and the Perl interpreter. Then the script can begin like this:
#! perl -w
A ScriptAlias directive sets up a directory that can be used for scripts written in any language. It's also possible to associate a directory with a specific language processor, so that any script found there is assumed to be written in a particular language. For example, to designate /usr/local/apache/cgi-perl as a mod_perl directory, you might configure Apache like this:
Alias /cgi-perl/ /usr/local/apache/cgi-perl/ SetHandler perl-script PerlHandler Apache::Registry PerlSendHeader on Options +ExecCGI
In this case, Perl scripts located in the designated directory would be invoked as follows:
http://apache.snake.net/cgi-perl/myscript.pl
Using mod_perl is beyond the scope of this book, so I won't say any more about it. Check Appendix C for some useful mod_perl resources.
Directories used only for scripts generally are placed outside of your Apache document tree. As an alternative to using specific directories for scripts, you can identify scripts by filename extension, so that files with a particular suffix become associated with a specific language processor. In this case, you can place them anywhere in the document tree. This is the most common way to use PHP. For example, if you have Apache configured with PHP support built in using the mod_php module, you can tell it that scripts having names ending with .php should be interpreted as PHP scripts. To do so, add this line to httpd.conf:
AddType application/x-httpd-php .php
Then if you install a PHP script myscript.php under htdocs (the Apache document root directory), the URL for invoking the script becomes:
http://apache.snake.net/myscript.php
If PHP runs as an external standalone program, you'll need to tell Apache where to find it. For example, if you're running Windows and you have PHP installed as D:Phpphp.exe, put the following lines in httpd.conf (note the use of forward slashes in the pathnames rather than backslashes):
ScriptAlias /php/ "D:/Php/" AddType application/x-httpd-php .php Action application/x-httpd-php /php/php.exe
For purposes of showing URLs in examples, I'm going to assume that Perl and Python scripts are in your cgi-bin directory, and that PHP scripts are in the mcb directory of your document tree, identified by the .php extension. This means that URLs for scripts in these languages will look like this:
http://apache.snake.net/cgi-bin/myscript.pl
http://apache.snake.net/cgi-bin/myscript.py
http://apache.snake.net/mcb/myscript.php
If you plan to use a similar setup, make sure you have a cgi-bin directory under your Apache root, and an mcb directory under your Apache document root. Then, to deploy Perl or Python web scripts, copy them to the cgi-bin directory. To deploy PHP scripts, copy them to the mcb directory.
If you request a web script and get an error page in response, have a look at the Apache error log, which can be a useful source of diagnostic information when you're trying to figure out why a script doesn't work. A common name for this log is error_log in the logs directory. If you don't find any such file, check httpd.conf for an ErrorLog directive to see where Apache logs its errors.
After Apache has been configured to support script execution, you can begin to write scripts that generate web pages. The remainder of this section describes how to do so for Perl, PHP, and Python. The examples for each language connect to the MySQL server, run a SHOW TABLES query, and display the results in a web page. The scripts shown here indicate any additional modules or libraries that web scripts typically need to include. (Later on, I'll generally assume that the proper modules have been referenced and show only script fragments.)
16.3.3.1 Perl
Our first web-based Perl script, show_tables.pl, is shown below. It produces an appropriate Content-Type: header, a blank line to separate the header from the page content, and the initial part of the page. Then it retrieves and displays a list of tables in the cookbook database. The table list is followed by the trailing HTML tags that close the page:
#! /usr/bin/perl -w # show_tables.pl - Issue SHOW TABLES query, display results # by generating HTML directly use strict; use lib qw(/usr/local/apache/lib/perl); use Cookbook; # Print header, blank line, and initial part of page print <Tables in cookbook Database
Tables in cookbook database:
EOF # Connect to database, display table list, disconnect my $dbh = Cookbook::connect ( ); my $sth = $dbh->prepare ("SHOW TABLES"); $sth->execute ( ); while (my @row = $sth->fetchrow_array ( )) { print "$row[0] "; } $dbh->disconnect ( ); # Print page trailer print < EOF exit (0);
To try out the script, install it in your cgi-bin directory and invoke it from your browser as follows:
http://apache.snake.net/cgi-bin/show_tables.pl
show_tables.pl generates HTML by including literal tags in print statements. Another approach to web page generation is to use the CGI.pm module, which makes it easy to write web scripts without writing tags literally. CGI.pm provides an object-oriented interface and a function call interface, so you can use it to write web pages in either of two styles. Here's a script, show_tables_oo.pl, that uses the CGI.pm object-oriented interface to produce the same report as show_tables.pl:
#! /usr/bin/perl -w # show_tables_oo.pl - Issue SHOW TABLES query, display results # using the CGI.pm object-oriented interface use strict; use lib qw(/usr/local/apache/lib/perl); use CGI; use Cookbook; # Create CGI object for accessing CGI.pm methods my $cgi = new CGI; # Print header, blank line, and initial part of page print $cgi->header ( ); print $cgi->start_html (-title => "Tables in cookbook Database", -bgcolor => "white"); print $cgi->p ("Tables in cookbook database:"); # Connect to database, display table list, disconnect my $dbh = Cookbook::connect ( ); my $sth = $dbh->prepare ("SHOW TABLES"); $sth->execute ( ); while (my @row = $sth->fetchrow_array ( )) { print $row[0] . $cgi->br ( ); } $dbh->disconnect ( ); # Print page trailer print $cgi->end_html ( ); exit (0);
The script includes the CGI.pm module with a use CGI statement, then creates a CGI object, $cgi, through which it invokes the various HTML-generation calls. header( ) generates the Content-Type: header and start_html( ) produces the initial page tags up through the opening
tag. After generating the first part of the page, show_tables_oo.pl retrieves and displays information from the server. Each table name is followed by a
tag, produced by invoking the br( ) method. end_html( ) produces the closing and tags. When you install the script in your cgi-bin directory and invoke it from a browser, you can see that it generates the same type of page as show_tables.pl.
CGI.pm calls often take multiple parameters, many of which are optional. To allow you to specify just those parameters you need, CGI.pm understands -name => value notation in parameter lists. For example, in the start_html( ) call, the title parameter sets the page title and bgcolor sets the background color. The -name => value notation also allows parameters to be specified in any order, so these two statements are equivalent:
print $cgi->start_html (-title => "My Page Title", -bgcolor => "white"); print $cgi->start_html (-bgcolor => "white", -title => "My Page Title");
To use the CGI.pm function call interface rather than the object-oriented interface, you must write scripts a little differently. The use line that references CGI.pm imports the method names into your script's namespace so that you can invoke them directly as functions without having to create a CGI object. For example, to import the most commonly used methods, the script should include this statement:
use CGI qw(:standard);
The following script, show_tables_fc.pl, is the function call equivalent of the show_tables_oo.pl script just shown. It uses the same CGI.pm calls, but invokes them as standalone functions rather than through a $cgi object:
#! /usr/bin/perl -w # show_tables_fc.pl - Issue SHOW TABLES query, display results # using the CGI.pm function-call interface use strict; use lib qw(/usr/local/apache/lib/perl); use CGI qw(:standard); # import standard method names into script namespace use Cookbook; # Print header, blank line, and initial part of page print header ( ); print start_html (-title => "Tables in cookbook Database", -bgcolor => "white"); print p ("Tables in cookbook database:"); # Connect to database, display table list, disconnect my $dbh = Cookbook::connect ( ); my $sth = $dbh->prepare ("SHOW TABLES"); $sth->execute ( ); while (my @row = $sth->fetchrow_array ( )) { print $row[0] . br ( ); } $dbh->disconnect ( ); # Print page trailer print end_html ( ); exit (0);
Install the show_tables_fc.pl script in your cgi-bin directory and try it out to verify that it produces the same output as show_tables_oo.pl.
This book uses the CGI.pm function call interface for Perl-based web scripts from this point on. You can get more information about CGI.pm at the command line by using the following commands to read the installed documentation:
% perldoc CGI % perldoc CGI::Carp
Other references for this module, both online and in print form, are listed in Appendix C.
16.3.3.2 PHP
PHP doesn't provide much in the way of tag shortcuts, which is surprising given its web orientation. On the other hand, because PHP is an embedded language, you can simply write your HTML literally in your script without using print statements. Here's a script show_tables.php that shifts back and forth between HTML mode and PHP mode:
Tables in cookbook Database
Tables in cookbook database:
"); mysql_free_result ($result_id); mysql_close ($conn_id); ?>
To try the script, put it in the mcb directory of your web server's document tree and invoke it as follows:
http://apache.snake.net/mcb/show_tables.php
Unlike the Perl versions of the MySQL show-tables script, the PHP script includes no code to produce the Content-Type: header, because PHP produces it automatically. (If you want to override this behavior and produce your own headers, consult the header( ) function section in the PHP manual.)
Except for the break tags, show_tables.php includes HTML content by writing it outside of the and ?> tags so that the PHP interpreter simply writes it without interpretation. Here's a different version of the script that produces all the HTML using print statements:
"); print ("
"); print ("Tables in cookbook Database "); print (" "); print (" "); print ("
Tables in cookbook database:
"); # Connect to database, display table list, disconnect $conn_id = cookbook_connect ( ); $result_id = mysql_query ("SHOW TABLES", $conn_id); while (list ($tbl_name) = mysql_fetch_row ($result_id)) print ("$tbl_name
"); mysql_free_result ($result_id); mysql_close ($conn_id); print ("
"); print ("
"); ?>
Sometimes it makes sense to use one approach, sometimes the otherand sometimes both within the same script. If a section of HTML doesn't refer to any variable or expression values, it can be clearer to write it in HTML mode. Otherwise it may be clearer to write it using print or echo statements, to avoid switching between HTML and PHP modes frequently.
16.3.3.3 Python
A standard installation of Python includes cgi and urllib modules that are useful for web programming. However, we don't actually need them yet, because the only web-related activity of our first Python web script is to generate some simple HTML. Here's a Python version of the MySQL status script:
#! /usr/bin/python # show_tables.py - Issue SHOW TABLES query, display results import sys sys.path.insert (0, "/usr/local/apache/lib/python") import MySQLdb import Cookbook # Print header, blank line, and initial part of page print """Content-Type: text/html
Tables in cookbook Database
Tables in cookbook database:
""" # Connect to database, display table list, disconnect conn = Cookbook.connect ( ) cursor = conn.cursor ( ) cursor.execute ("SHOW TABLES") for (tbl_name, ) in cursor.fetchall ( ): print tbl_name + "
" cursor.close ( ) conn.close ( ) # Print page trailer print """ """
Put the script in Apache's cgi-bin directory and invoke it like this:
http://apache.snake.net/cgi-bin/show_tables.py
16 4 Using Tomcat to Run Web Scripts |
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References