MySQL

Using MySQL with CGI scripts

Using MySQL with Common Gateway Interface scripts will allow you to develop more interactive web sites. Examples of using CGI scripts with MySQL are searchable catalogs, user account management, inventory tracking, and information management. Any time you have even small quantities of data which are similar and/or which will change over time, a database solution will likely be useful.

CGI scripting does require programming experience. If you are not familiar with CGI scripting, it is suggested that you begin with the basics of forms and non database applications. There are many books available to teach you CGI programming in a number or languages. Here we will be focusing on how to program MySQL using Perl as the CGI scripting language.

A Quick Review of How CGI Works

Normally clicking on a link in a web browser causes the web server to return a static .html page. No matter who clicks on this link or how many times they do it, the resulting returned web page is always the same. To change a static .html page the site's webmaster must edit the contents of the .html file.

On the other hand, a CGI script allows a link or a button in a web page to run a program on the web server. This program can do any number of things from getting the current date and time to performing a complex lookup and update in a database. In either case, the results are not the same everytime the link or button is pressed.

The process occurs something like this:

User clicks on a link in a web page (e.g. http://www.cgitest.com/cgi-bin/test.cgi).

The web server runs the program test.cgi.

The test.cgi program does what it is programmed to do.

The test.cgi program also builds a .html file in memory and sends it back to the user's browser.

It is the last two steps which make CGI scripts so useful. The program can perform what ever operations it needs to and it can then generate a .html page based on the results of these operations. When the CGI script is used with a database such as MySQL, many things are possible. Generally, the page returned to the user's browser contains the results of the database search. Or, if the user had provided information through a form in the web page, the database records were updated.

Using Perl to Access a MySQL Database

The programming language Perl can be used to access a MySQL database. It is the language we will use for our examples. Access to MySQL using Perl requires the Perl DBI module. Both Perl and the DBI module are installed and available to use through your web site account.

The following code example sets up a connection the database to the www.yourwebsite.com database, prepares and executes an SQL statement, stores the result in a local variable, and then cleans up the connection.

# Use the DBI module
use DBI qw(:sql_types);

# Declare local variables

my ($databaseName, $databaseUser, $databasePw, $dbh);
my ($stmt, sth, @newRow);
my ($telephone);

# Set the parameter values for the connection
$databaseName = "DBI:mysql:yourWebSite_com";
$databaseUser = "yourLoginId";
$databasePw = "yourLoginPassword";

# Connect to the database
# Note this connection can be used to 
# execute more than one statement
# on any number of tables in the database

$dbh = DBI->connect($databaseName, $databaseUser, 
    $databasePw) || die "Connect failed: $DBI::errstr\n";

# Create the statement. 
$stmt = "SELECT Name FROM Phonebook  
         WHERE (Telephone LIKE '518%')";

# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt) 
    || die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";

# Get the first record
# If more than one record will be returned put
# the fetchrow in a while loop
@record = $sth->fetchrow()

# Get the value of the first field returned.
$telephone = $record[0];

# Clean up the record set and the database connection    
$sth->finish();
$dbh->disconnect();

All queries follow the same basic formula. Simply replace the SELECT statement with the INSERT, UPDATE, etc. statement you wish to use. Note that these other queries do not return records. So, the fetchrow() and assignment which follows should be deleted for then.

Many other operations such as joins, subqueries, grouping, and sorting are all supported by providing a proper SQL statement in place of the one above.