Grabber Softwares: Enhance Your Computing
Query Data with a Web Form

We’ve built a MySQL database for Linux. Now let’s make data retrieval easier

In In last issue, of Pcquest the essentials of setting up a database server in Linux. They have 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 (http://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
<html> <head><title>WWW-SQL Test</title></head> <body>

This is an output from the WWW-SQL CGI script. My name is: <!sql print $answer>

</body></html>
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 CGI_DIR=<cgi-dir>. Look into your cgi-bin directory to make sure that www-sql binary has been copied there.

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:

<! SQL command arg1 arg2 ...>

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.

<html>
<head><title>Contact for Seminar on WEB-database integration</title></head>
<body>
<H1>Contact Addresses of Participants</H1>
<!— Let user change query —>
<form action=seminar.html>
<input name=surname > <input type=submit><br>
</form>
<! sql connect >
<! sql database contacts >
<! sql setdefault surname "-" >
<! sql query "select * from addresses where lastname = ‘$surname’ " q1 >
<! sql if $NUM_ROWS != 0 >
<!— Put in table —>
<table>
<tr> <th>First Name</th> <th>Last Name</th> </tr>
<! sql print_rows q1 "<tr> <td>@q1.0</td> <td>@q1.1</td> </tr>\n" >
</table>
<p>
<center><em>Page produced by WWW-SQL</em></center>
</body>
</html>

That’s all! Save this in seminar.html in your html directory and call it in your browser with 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!

Gopal Upadhyaya is an IT consultant.