MySQL for E-Commerce Web Sites

Werner Klauser

Today's e-commerce Web sites do not have dry HTML-coded Web pages. Their dynamic Web pages need to react to changing situations. Whether this situation involves constantly changing inventory, orders being processed, up-to-date sport scores, or live share prices, the Web pages must reflect these changes. You want to constantly re-code the HTML-coded Web pages, but wouldn't it be cool if the Web pages somehow adapted themselves to a constantly changing database? Why is MySQL often used on e-commerce Web sites? For that matter, what is MySQL, and what can you do with it? In this article, I will provide an overview of MySQL and its uses.

Introduction

MySQL can be used to get a small-scale SQL system to run. With small scale, I do not mean small; I mean not large scale, such as a major airline booking system. MySQL (pronounced “My Ess Que Ell”, not MY-SEQUEL) is a true multi-user, multi-threaded SQL database server using SQL, the most popular and standardized database language in the world. MySQL is a client/server implementation that consists of a server daemon mysqld and many different client programs and libraries. The main goals of MySQL are speed, robustness, and ease of use.

MySQL was originally developed because an SQL server was needed that could handle very large databases of a magnitude faster than what any database vendor could offer on inexpensive hardware. The development team has been using MySQL since 1996 in an environment with more than 40 databases containing 10,000 tables, of which more than 500 tables have more than 7 million rows. This is about 100 GB of mission-critical data.

Because speed was a goal of MySQL's development team, various functions common to a commercial SQL system are missing. These are things such as transactions, stored procedures, locks, views, and triggers. But even with these missing functions, much can be accomplished with MySQL. Originally, (1979!) MySQL was called mSQL and written in BASIC. Later it was named MySQL, and developed on Solaris and Linux systems by T.c.X. DataKonsultAB, a Swedish company. It is now available on every system that supports GNU's configure. This includes Microsoft operating systems.

MySQL Costs

For normal use, MySQL costs nothing. Only when you sell MySQL directly or as a part of another product do you have to pay for it. The current price is $200 for one license, and $1000 for 10 licenses. What does this mean if you are running a commercial Web server using MySQL? In this case, because you are not directly selling MySQL, you are not required to purchase a MySQL license. However, the designers of MySQL would like you to purchase email MySQL support, which costs $200 per year.

Exceptions to this are Microsoft operating systems. There is a shareware version of MySQL called MySQL-Win32, which may be used for evaluation purposes, but a usage license is required. A license is required under Microsoft Windows because Windows is a highly commercial operating system, with very high development costs. (It usually takes 3 times longer to build and test things under Windows.)

Installing MySQL

MySQL can be downloaded from: http://www.mysql.com, or from one of its many mirrors. Although its source could be downloaded, it is rarely necessary since you're sure to find the proper binary, which includes manual pages, both in .txt and .html format. In my case, I used mysql-3.22.32-sun-solaris2.7-sparc. The general installation steps are:

$ gunzip < mysql-VERSION-OS.tar.gz | gtar xvf -
$ ln -s mysql-VERSION-OS mysql
$ cd mysql
$ scripts/mysql_install_db

The only other step to properly (safely) start the MySQL daemon mysqld, is the following:

$ bin/safe_mysqld &

Included in the downloaded installation README, is a pointer to a script that must be installed as /etc/rc3.d/S99mysql to automatically start the MySQL server daemon when the system starts.

Now that the MySQL daemon is properly running, the next step is to use MySQL with your own database.

Your First Database

Instruct MySQL to create your database with its mysqladmin command:

$ bin/mysqladmin create database999

Start your MySQL session with this newly created database:

$ bin/mysql database999

After some introductory remarks, you will begin using MySQL's shell. Of course, you can enter new commands, but it also supports a history of your past commands. These entries are addressed with the cursor-up and cursor-down keys, and can be edited interactively using almost all of your keyboard's keys.

Create a Table

mysql> create table people (name varchar(20), \
    first varchar(20), address varchar(60));

Note that each SQL command ends with a semi-colon. See how MySQL describes your table with the desc SQL command:

mysql> desc people;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| first   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(60) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Now insert two records describing your favorite friends:

mysql> insert people values ('Doe', 'John', \
   'his address');
mysql> insert people values ('Doe', 'Jane', \
   'same address as John Doe');

View the new data:

mysql> select * from people;

+------+-------+--------------------------+
| name | first | address                  |
+------+-------+--------------------------+
| Doe  | John  | his address              |
| Doe  | Jane  | same address as John Doe |
+------+-------+--------------------------+

Or select only one person with a more constraining select:

mysql> select * from people where first = 'Jane';

+------+-------+--------------------------+
| name | first | address                  |
+------+-------+--------------------------+
| Doe  | Jane  | same address as John Doe |
+------+-------+--------------------------+

Update information:

mysql> update people set address = 'John\'s \
   new address' where first = 'John';

These are the simpler SQL commands supported my MySQL. The next step is to be able to access and modify the database using dynamic HTML Web pages.

Using MySQL with Dynamic HTML Web Pages

Before the dynamic HTML Web pages that will be using Perl can access the database, you must download some Perl modules. One method is to download the proper Msql-Mysql modules from MySQL's download site, and then properly configure and make these modules. An easier method is to use Perl's CPAN (Comprehensive Perl Archive Network). If you don't have this valuable aid, download it from:

http://www.cpan.org/modules/bymodule/CPAN

Install it:

$ perl Makefile.PL
$ make
$ make test
$ make install

Or, use the CPAN module to retrieve and install the two necessary modules:

$ perl -MCPAN -e "install CGI"
$ perl -MCPAN -e "install DBD::mysql"

An advantage of CPAN is that it automatically downloads and installs necessary prerequisites. Now you can begin with the Perl CGI scripts. You need to do the following:

• Connect with your database.
• Create your SQL statement.
• Use prepare to send it to the database server.
• Execute the SQL command.
• Fetch the results.
• Finish the statement.
• Disconnect from the database.

Here's a short example:

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:database999");
my $sth = $dbh->prepare("select name, first from people");
$sth->execute();
my ($first, $name);
$sth->bind_col(1, \$name);
$sth->bind_col(2, \$first);
while ( $sth->fetch() ) {
    print("$first $name\n");
}
$sth->finish;
$dbh->disconnect;

Note that this simple example has no error handling. This would certainly not be useable for a commercial Web site. This example can be executed from the command line and prints the names found in database999's people table. The next step is to add the necessary lines making it a cgi-bin executable Web page with an appropriate Web design:

#!/usr/bin/perl -w
use strict;
use DBI;
use CGI;

my $query = new CGI;
print $query->header;

my $dbh = DBI->connect("DBI:mysql:database999");
my $sth = $dbh->prepare("select name, first from people");
$sth->execute();
print("<html><body><table border=1><th colspan=2>Name</th>");
my $name;
$sth->bind_col(1, \$name);
$sth->bind_col(2, \$first);
while ( $sth->fetch() ) {
    print("<tr><td>$first</td><td>$name</td></tr>");
}
print("</table></body></html>");
$sth->finish;
$dbh->disconnect;

Only three lines were added to allow CGI use. Some HTML code was added to print's output to make a useable table of the people's names. Simple, isn't it?

An E-commerce Example Using MySQL

I was involved in developing the B2B Web site http://www.chipexchange.com. Chipexchange is an Internet-based trading platform designed to allow the electronic chip industry to source and sell standard components. Contracts are concluded between a buyer who entered a buying request with a maximum price on the platform, and the seller who, during the auction procedure, bids the lowest acceptable price. These transactions and reverse-auctions are saved using MySQL's database addressed using Perl scripts.

An in expensive but quality database in the form of MySQL allows your Web pages to adapt themselves to your constantly changing business. That's a necessary step for a successful e-commerce Web site.

About the Author

Werner Klauser is an independent UNIX consultant working near Zurich, Switzerland. While not paragliding, enjoying his girls, or roarin' around on his Harley chopper, he can be reached at klauser@klauser.ch or via his Web page www.klauser.ch.