Setting Up a MySQL Based Website - Part I

By: Andrew Chen
Friday, October 1, 1999 05:28:32 PM EST
URL: http://www.linuxplanet.com/linuxplanet/tutorials/1046/1/

Introduction

In this age of the full-service internet, the demand for bigger and badder websites will only increase. To meet the demands of these full-service websites, the SQL database servers come into play. One of the more popular SQL servers available for download on the internet is MySQL. MySQL is not released under a full GPL free license, but for some applications, like intranets, it is free to use. For most users, a $200 license will have to be purchased. However, this price compares very favourably with other companies producing database backends for the commercial market, like IBM, Oracle and Sybase.

Getting Started

For the purpose of this demonstration, we will be setting up a guestbook, using a perl and a MySQL backend. An SQL backend solution for this application would be most appropriate for companies such as GuestWorld who run free public guestbooks.

perl is a scripting language and this article demonstrates its powerful features, as much as those of MySQL.

The Tools

The absolute earliest version of perl you want to be working with is 5.004. You may be able to get away with 5.003, but perl 4 must be avoided. Perl 4 is considered a security hazard and lacks many of the basic functions we will need to write our guestbook application. In addition to the standard perl distribution, you will be needing the DBI module and DBD::mysql module from CPAN. The DBI module is a general API for accessing databases. The DBD::mysql module can be considered a plug-in for DBI, allowing DBI to talk to MySQL servers. For the purpose of this demonstration, we used a pre-compiled MySQL daemon from ftp.mysql.com. To demonstrate the application, we will assume that a copy of Apache or some other webserver has already been properly installed and configured.

The great thing about DBI is that it works with any type of database. If your organisation uses Sybase, for instance, you should be able to pick up a Sybase DBD driver and use it to run the perl code included with this article.

The System

Most any system can run a MySQL server; however, it is recommended that you use at least a low-end Pentium class machine. For this demonstration, I`ll be using a Slackware Linux 4.0 machine on a Celeron 366 chip.

Slackware isn't necessary either; the techniques described below will work with any distribution. If you use a package manager then all the tools described should install automatically without much fuss, as they are mature products with high levels of stability.

Installing MySQL

Installing MySQL couldn`t be easier. After downloading the appropriate tarball from my local MySQL mirror, in this case mysql-3.22.23b-pc-linux-gnu-i686.tar.gz,
the normal un-gziping yielded me the tar file. Before un-taring your package, first decide where you want to install MySQL. MySQL itself comes ready-to-run out of the package. Hence, you should untar MySQL in its final resting place. After untaring, cd to the distribution directory and run ./configure . This allows the daemon to set up the appropriate tables and then start up. You should see something like this after running ./configure :

Creating db table
Creating host table
Creating user table
Creating func table
Creating tables_priv table
Creating columns_priv table
...etc...
Starting mysqld daemon with databases from /usr/local/mysql/data

If you see a message staying that mysqld has created all the tables and started up the mysqld daemon without incident, then congratulations; you now have a functioning MySQL installation.

Installing the Perl Modules

The next step is to install the two necessary modules. The best way to go about doing this is to use the CPAN shell. CPAN, the Comprehensive Perl Archive Network, is the world's largest collection of perl modules. You first need to install DBI, the database interface module. This can be done by typing:

perl -MCPAN -e 'install DBI'

If perl should return an error such as Unrecognized switch: -MCPAN, it means you have perl 4 installed. Fear not, though, your administrator may have installed perl 5 later on in your path. You may want to try

/usr/local/bin/perl -MCPAN -e'install DBI'

/usr/bin/perl -MCPAN -e'install DBI'

This command invokes the CPAN shell, a tool that comes bundled with every version of perl to help facilitate the installation of modules. If this is the first time you`ve used the CPAN shell, you will be prompted with a few configuration questions. If you are running CPAN shell as root, you are probably okay if you select the defaults, and pick a CPAN mirror close to you. If you are non-root, you will want to specify PREFIX=/your/home/directory when asked for parameters for perl Makefile.PL . It should look something like this:

Parameters for the 'perl Makefile.PL' command? PREFIX=/my/home/dir

What this tells perl to do is install the modules inside your home directory, instead of the public perl modules directory. If all has gone well, you should see something similar to this:

/usr/bin/make install -- OK

After you have a successful DBI installation, you will need to install the DBD::mysql DBI plugin. This can be done by executing:

perl -MCPAN -e'install DBD::mysql'

You will be asked a series of questions needed for installation. When asked what kind of drivers you wish to install, be sure to specify 'MySQL only.` When asked if you want MysqlPerl emulation, it is typically safe to say 'n` When asked where your MySQL include directory is, specify the directory that you untared your MySQL distribution in. You can safely hit <Enter> to the remaining configuration options. Once again, if you receive:

/usr/bin/make install -- OK

then you have installed DBD::mysql without incident. Congratulations.

Setting up the Database

In order for us to have a place to store our guest book information, we must create a properly configured database. Within a database is a table. In the table, there are columns that define the names and data types. Although MySQL can have multiple tables within a single database, for our purposes we will use only one. MySQL database uses the term 'columns` to describe each individual field in a table. To setup our database, we use the mysqladmin tool.

mysqladmin -uroot create guestbook

will create our database for the guestbook. Since MySQL employs a user access system, we must tell MySQL who has access to which databases. Several commands need to be executed to setup the permissions:

mysql -uroot -e"insert into host(Host,Db) values('localhost','guestbook')" mysql

mysql -uroot -e"insert into db(Host,Db,User,Select_priv,Insert_priv) values('%','guestbook','guestbook','Y','Y')" mysql

mysql -uroot -e"insert into user(Host,User,Password) values('localhost','guestbook',password('guestbook')" mysql

mysqladmin -uroot reload

What the first statement tells MySQL, is to allow anyone from localhost access the database guestbook. The second statement says allow the user accessing guestbook from any host to SELECT and INSERT into the database. The third statement sets the user guestbook access from localhost, using the password guestbook. We call the function password() for guestbook in order to encrypt the password. The last statement tells MySQL to reload the user files.

The final step of MySQL configuration is to create the table to hold all our guestbook entries. To do this, we can execute:

mysql -uroot -e" CREATE TABLE guestbook (

name char(255) not null,
age int(3) unsigned,
email char(255) not null,
website char(255),
comments blob,
time int(10) unsigned
);" guestbook

Now that you`ve finished creating users and tables, create the perl application for accessing and adding to the database.

Writing the Perl Application

The following perl application will both display a form for user entries into the guestbook, to add new entries into the guestbook and then display the guestbook.

#!/usr/bin/perl -Tw

use strict;
$| = 1;

use CGI::Carp "fatalsToBrowser";
use CGI ":all";
use DBI;

my $serverName = "localhost";
my $serverPort = "3306";
my $serverUser = "guestbook";
my $serverPass = "guestbook";
my $serverDb = "guestbook";
my $serverTabl = "guestbook";

print
     header,
     start_html("SQL Guestbook"),
     h1("Add and Read Guestbook Entries");
if(my $error = check_form()) {
     show_form($error);
     print end_html;
} else {
     if(my $error = insert_entry()) {
          show_form($error);
     } else {
          show_entries();
     }
     print end_html;
}

sub show_form {
     my $error = shift;
     print hr;
     if($error) { print $error, hr; }
     print
          start_form,
     table(map
          Tr(td($_->[0]), td(textfield($_->[1],"",undef,60))),
          ["Name", "name"],
          ["Age", "age"],
          ["E-Mail Address", "email"],
          ["Web Site Address", "website"],
          ["Comments", "comments"],
          ),
     submit,
     end_form,
     hr;
}

sub check_form() {
     return "You didn't enter anything..." unless param();
     return "Please enter a name" unless param("name");
     return "Please enter your e-mail address" unless param("email");
     return;
}

sub insert_entry {
     my ($dbh, $success, $name, $age, $email, $website, $comments,$time);
     
     $dbh = DBI->connect("DBI:mysql:database=$server Db;host=$serverName;port=$serverPort",$serverUser,$serverPass);
     $name = param("name");
     $age = param("age");
     $email = param("email");
     $website = param("website");
     $comments = param("comments");
     $time = time;
     $success = $dbh->do("INSERT INTO
               $serverTabl(name,age,email,website,comments,time)
               VALUES(?,?,?,?,?,?)", undef, $name, $age, $email, $website, $comments, $time);
     $dbh->disconnect;
     if($success != 1) {
          return "Sorry, the database was unable to add your entry.
               Please try again later.";
     } else {
          return;
     }
}

sub show_entries {
     my ($dbh, $sth, @row);

     $dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
     $sth = $dbh->prepare("SELECT name,age,email,website,comments,time
               FROM $serverTabl ORDER BY time");
     $sth->execute;
     print "Existing Entries",hr;
     while(@row = $sth->fetchrow_array) {
          $row[5] = scalar(localtime($row[5]));
          print "Name: ", $row[0], br;
          print "Age: ", $row[1], br;
          print "E-Mail Address: ", $row[2], br;
          print "Web Site Address: ", $row[3], br;
          print "Comments: ", $row[4], br;
          print "Added on ", $row[5], hr;
     }
     $sth->finish;
     $dbh->disconnect;
}

In Closing...

This is only a very simple representation of what you can do with MySQL. The applications of databases in today`s internet are limitless. As for this guestbook application, there are several improvements that can be made, including the filtering of some malicious HTML, and options to display only a certain number of entries on one page. For more information on MySQL, be sure to check out the MySQL webpage at http://www.mysql.com/ . For more information about the perl DBI interface, try http://mysql.turbolift.com/mysql/DBD_3.21.X.html.

Copyright Jupitermedia Corp. All Rights Reserved.