Integrating Apache and MySQL

We have seen how to setup Apache, program and create CGIs in Perl, setup MySQL and access MySQL using the DBI Perl module. In this tutorial we will see how to write CGIs using Perl, the DBI module, to access a MySQL table and display the results using the Apache Web server. Then we will discuss briefly the software integration of Perl, DBI, MySQL in Apache. PHP is also being integrated in this suite to add embedded scripting into pages.

Basic concepts

The model below summarizes the various elements needed to receive a query on the Web, search a MySQL table and display a Web page with the results of the query.

The first element is a form to perform the query. The form will define fields (parameters) and accept values for the query. It will also define the script path (server,directory, script name) and the method to be used (typically post). The field names in the form do not need to be the same as in the MySQL table, but should be at least similar to simplify writing the script. When the submit button is pressed the query is sent to the Web Server.

The Web Server will send the parameters to the script server, which will run the script. In the case Perl is used to decode the parameters (using CGI.pm or cgi-lib.pl), and convert the query into an SQL command (using the DBI.pm to connect, prepare and execute the query).

The MySQL server will receive the query as SQL commands, process it, and return rows of results, which were requested by the Perl script. The script server receives the rows of results and convert them to results formatted as HTML (using Perl and CGI.pm or cgi-lib.pl).

Finally, the Web Server sends the Web page with the results back to the browser that initiated the query.

Getting started

The first thing to remember is that scripts need to be placed in the Web Server script directory, typically cgi-bin. Moreover the test of the script as a CGI is done using a browser (not the shell window) with an URL like http://localhost/cgi-bin/scriptname. Of course if you are using Linux or Unix, do not forget to make it readable and executable by apache, or another account you have assigned or created for the Apache Server (if you don't, you will see the error message "Internal Server Error ..." and you will find "Permission denied" in the server log -- /etc/httpd/logs/error_log). In Linux, when apache is installed an account and group apache are created. I suggest you change the ownnership of the script to apache and give read and execute to it and nothing to anybody else, as shown in this image. You do not need to have any of these concerns in Windows because, as you know, Windows give permission to everyone do everything ...

Remember to start the apache and mysql servers before you run any script to access a table in MySQL using your Apache Web server. In Windows open Computer Management, Services and start each service in turn. In Linux using the KDE desktop open services applet and start both httpd and mysqld services.

Examples

First example

In a first example we see a Perl script which displays the contents of the table people from the MYTEST database, which we created in a prior tutorial. We also used the username and password used in the same prior tutorial. 

#!/usr/bin/perl  -w

#include libraries
use DBI;                             #include the DBI.pm module 
use  CGI qw(:standard);              #include the standard methods of the CGI.pm module
use CGI::Carp qw(fatalsToBrowser);   #include the error reporting method from CGI.pm.

#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="pwserver";
#$query = new CGI;           #only use if you prefer the -> notation

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                                        $user,
                                        $password) 
                                       or die "Can't connect to database: $DBI::errstr\n";

#prepare the query
my $sth = $dbh->prepare( "SELECT name,address FROM people");

#execute the query
$sth->execute( );

# Prepare HTML headers (you can do this in many ways)
print header, start_html (-title=>"Content of Table people",-bgcolor=>'white');
#print $query->header;       #only use if you prefer the -> notation
#print $query->start_html (-title=>"Content of Table people",-bgcolor=>'white'); #same
#print "<HTML>\n<HEAD>\n<TITLE>Content of Table people</TITLE>\n</HEAD>\n<BODY bgcolor=white>\n";
print "<H1>Content of Table people</H1>\n";
print "<TABLE border=1>\n";
print "<TR><TH> Name</TH>><TH>Address</TH>\n";

## Retrieve the results of a row of data and  send to browser

while ( ($name,$address) = $sth->fetchrow_array( ) )  {
        print "<tr><td>$name</td><td>$address</td></tr>\n";
}

#end the HTML
print "</table></body></html>\n";

#end program
$sth->finish( );
$dbh->disconnect( );
exit;
Please notice that very little was needed to make the script into a CGI, and that we used a mix of CGI.pm methods with print together with plain HTML.

Second Example

Each database software has its way of quoting strings. If you just use the Perl quote, like in $myquote = "This is what needs to be quoted"; and use this in a WHERE clause in SQL, like in ... WHERE sentence = $myquote you will receive an error. DBI has a method quote which converts the Perl quotes into the specific database quote format, as shown in this simple example.

Our second example deals with the problem of quotes in CGIs, in the database environment.

#!/usr/bin/perl  -w

#include libraries
use DBI;
use  CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);

#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="pwserver";
my $name = "Al Bento";

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                                        $user,
                                        $password) 
                                       or die "Can't connect to database: $DBI::errstr\n";

#convert the quoted Perl value to DBI quoted Perl 
my $qname = $dbh->quote( $name);

#prepare the query
my $sth = $dbh->prepare( "
              SELECT name,address 
              FROM people WHERE name =$qname");

#execute the query
$sth->execute( );

# Prepare HTML headers 
print header, start_html (-title=>"Content of Table people",-bgcolor=>'white');
print "<H1>Content of Table people</H1>\n";
print "<TABLE border=1>\n";
print "<TR><TH> Name</TH>><TH>Address</TH>\n";

## Retrieve the results of a row of data and  send to browser

while ( ($name,$address) = $sth->fetchrow_array( ) )  {
        print "<tr><td>$name</td><td>$address</td></tr>\n";
}

#end the HTML
print "</table></body></html>\n";

#end program
$sth->finish( );
$dbh->disconnect( );
exit;
Of course, all values of a TEXT INPUT field in a form are quoted values, and you may wish to clean extra blanks, convert or not to lowercase, etc, like we saw previously when we studied forms and flat files.

Third example

In this final example we receive input from a form using the method POST and query a MySQL table. The form is shown here.

#!/usr/bin/perl  -w

#include libraries
use DBI;
use  CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);

#definition of variables
$db="MYTEST";
$host="localhost";
$user="root";
$password="pwserver";

my $name = param('name');      # the ONLY line different from the prior example

#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                           $user,
                           $password) 
                           or die "Can't connect to database: $DBI::errstr\n";

#convert the quoted Perl value to DBI quoted Perl 
my $qname = $dbh->quote( $name);

#prepare the query
my $sth = $dbh->prepare( "
              SELECT name,address 
              FROM people WHERE name =$qname");

#execute the query
$sth->execute( );

# Prepare HTML headers 
print header, start_html (-title=>"Content of Table people",-bgcolor=>'white');
print "<H1>Content of Table people</H1>\n";
print "<TABLE border=1>\n";
print "<TR><TH> Name</TH>><TH>Address</TH>\n";

## Retrieve the results of a row of data and  send to browser

while ( ($name,$address) = $sth->fetchrow_array( ) )  {
        print "<tr><td>$name</td><td>$address</td></tr>\n";
}

#end the HTML
print "</table></body></html>\n";

#end program
$sth->finish( );
$dbh->disconnect( );
exit;

Surprise, surprise! All I had to do was to change one line and use the param('name') method to read the name sent from the query. Of course, you noticed that I worked hard to create these three examples ...

Integrating Perl, MySQL and PHP in Apache

As we will discuss ahead one of the major burdens of using CGIs is the need to load the script interpreter (Perl interpreter) every time a script runs. If we have 1,000 requests for a Perl script, not only we will load 1,000 times the script (which normally are small), but we will also load 1,000 times the Perl interpreter!!!

The solution is to integrate Perl with Apache: load the mod_perl for Apache -- this links one copy of the Perl interpreter and its libraries to one copy of the Apache server (please note that Apache normally starts more than one copy of itself to increase performance). This eliminates for all practical purposes the CGI/Perl overhead and makes Perl CGIs very fast.

The mod_perl installation in Windows can be automated using ppm, as we have seen to install DBI. ActivePerl, however does not keep mod_perl in their archives and you need to issue the intall command providing the URL of where the mod_perl is available. You can see my use of ppm at the command prompt to install mod_perl in this file. Also read the original documentation in this site.

It is beyond the scope of this tutorial to describe the installation process of the mod_perl in Linux, but you can see it here.Most distributions (for example Fedora) install it when you install Apache. To test if you already have it installed log as root and type: rpm -qa | grep mod_perl .

The DBI interface provides integration of MySQL with Perl, and, when the mod_perl is used, a wrapper around DBI->connect transparently maintains persistent connections -- Apache::DBI.

PHP, the last piece of the modern Apache 'suite' of tools, is similar to ASP, embedding code in a regular Web page, identified by the extention .php or .php3. PHP is a project of the Apache Software Foundation, the same group which supports the Apache Server project. You can download and install it as any other application in Windows. In Linux you should test if it is already installed by using: rpm -qa | grep php, similar to what you did for mod_perl. If it is not installed you can download it from here. Again, many modern Linux distributions install PHP when you install Apache.

In Linux you should download the sources for Apache, SSL, PHP, etc and compile them together to obtain the best integration among these various packages. See this tutorial on how to do it.

This page is maintained by Al Bento who can be reached at abento@ubalt.edu. This page was last updated on November 7, 2004. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.