In

our last issue, we talked about the essentials of setting up a database server in

Linux. We discussed the setup and use of MySQL–a robust, fast, and scalable database

server. In this article, we’ll build a Web interface to query our database.

There are many ways by which you can access your SQL database from Web pages. MySQL

documentation lists the various programs available to do this. Some of them like PHP, a

server-side HTML-embedded scripting language (www.php.net),

are powerful tools that you can use to develop sophisticated database-enabled Web pages.

PHP 3 is one of the hottest scripting languages to be found on the Internet. It gives many

capabilities to Web programmers, and the fact that PHP 3 code can be inserted directly

alongside HTML, makes it all the more convenient.

But, you need to have the language interpreter installed and configured on the Web

server. (The Red Hat distribution on the PCQ March ’99 CD has PHP installed as

modules in the Apache Web server.) Another option is to use DBD (database drivers) along

with the DBI Perl modules. These let you perform database administration functions from

within Perl programs, and you can write Perl CGI scripts to access your MySQL databases.

However, you need to be fairly proficient in Perl programming.

For our purpose, we’ll use a neat, little, easy-to-implement CGI

program–WWW-SQL. It’s designed to create Web pages from MySQL databases on the

fly. WWW-SQL is a CGI program that pre-processes HTML files for special embedded tags that

are basically commands to lookup MySQL databases, and inserts the results into an HTML

document. WWW-SQL is available at www.daa.com.au/~james/www-sql/

or ftp://ftp.daa.com.au/pub/james/www-sql/.

Once downloaded, compiling the source code is a simple process. Just type ./configure

followed by make in the download directory. There should be no problems if MySQL

files were installed as specified in the previous issue ("A Databse Server in

Linux", page 126). Otherwise, you’ll need to set up environment variables that

point to where your MySQL include and lib files are located. The WWW-SQL

documentation explains how to do this.

To test if the compilation was successful, type the following at the Linux prompt:

cat << EOF > test.in

This is an output from the WWW-SQL CGI script. My name is:

EOF


env REQUEST_METHOD=GET QUERY_STRING=’MYSTICAL+WEB’ \


PATH_TRANSLATED=test.in REMOTE_ADDR=127.0.0.1 \


REMOTE_HOST=localhost www-sql

This should write a small HTML document that says,

"This is an output from the WWW-SQL CGI script. My name is "MYSTICAL WEB"

to the standard output."

The next step is to install the binary. This is done by the make install command.

If your CGI-BIN directory is not one of /home/httpd/cgi-bin, /var/lib/httpd/cgi-bin,

or /usr/local/etc/httpd/cgi-bin, you must specify the directory on the command

line:

make install

Before you begin writing and using WWW-SQL HTML documents, you’ll need to set up

the default user account for WWW-SQL. This is done by changing to MySQL directory and by

typing the following commands:

mysql -u root -p pass mysql << EOF



insert into user (Host, User, Password, Select_priv)


values ("localhost", "nobody", "", "Y");


EOF


bin/mysqladmin -u root -p pass reload

Here "pass" is the root password. This will give www-sql access to all your

databases.

You can also restrict access to only some of the databases by making appropriate

entries in the mysql user and db tables. The WWW-SQL documentation explains

how to do this.

Every time you make entries in the mysql privilege tables, remember to issue mysqladmin

reload command.

You are now ready to create your HTML pages that’ll access your SQL databases.

All you need to do is embed special WWW-SQL commands in your HTML document. The WWW-SQL

command set contains various instructions for conditional execution, execution of queries,

and expansion of CGI variables and query fields.

Refer to the WWW-SQL documentation for the syntax of tags and the available commands.

In brief, you’ll insert tags of the following format into your HTML document:

where command is a command recognized by WWW-SQL, arg1 arg2 ... are

arguments for the specified command.

Let’s try writing a simple HTML file to query the addresses in the contacts

database created in the previous issue.

Contact Addresses of Participants

q1 >

\n" >

That’s all! Save this in seminar.html in your

html directory and call it in your browser with href="http://localhost/cgi-bin/www-sql/seminar.html">http://localhost/cgi-bin/www-sql/seminar.html.

You can now query your database of addresses by the last name of individuals. Once you get

familiar with the small set of WWW-SQL commands and master the art of building SELECT

statements you can write more sophisticated queries. You are now all set to put some

dynamism into your Web pages.

Logging out

Well, this was a quick run through the basics of setting up an SQL server and accessing

databases from Web pages. If you’ve managed to get things working, as described, you
would have gained some insights into how the different pieces fit together.

We have used MySQL and WWW-SQL, in our example, because they are easy to install and

get started. What is more, the additional tools to transfer existing databases from xBase

or Access tables into MySQL databases should give you a very good start in getting your

database-enabled Website up and running in no time. If you want more power to database

access from the Web, you can look at PHP 3 mentioned briefly in this article. Or you could

learn PERL programming!