Web FrontEnds for MySQL

HTML, the popular Web interface is also an easy to use front-end for many applications including databases. A server-side scripting language can be used to interact with databases like MySQL. Hence, we can have a Web front-end using HTML forms where one can type in the input data and subsequently click the submit button which would append the data to the database. Similarly, scripts can be used to pull out data from the database and present the data using HTML elements like tables.

We’ll look at setting up Perl, PHP, and JSP (Java Server Pages) scripting engines on PCQ Linux 7.1 (given with PCQuest July 2001). We’ll use the Apache Web server to set up the former two engines. We then set up a standalone Tomcat (JSP engine) for JSP. Then we’ll see how each of these scripting languages can be used to interact with

MySQL.

Pre-requisites



We’ll assume that you have set up and have a running Apache Web server. If not, refer to the articles, Setting up Web servers (page 106) and Configuring Apache Web Server (page 81) in PCQuest’s June and October 2001 issues, respectively. You can also use the archives on this month’s CD to search and read the articles.

Setting up PHP



First you must have the Apache development libraries installed. Issue the command:

rpm -qa | grep apache-devel

If you don’t get any output, then install the RPM from the PCQ Linux CD 2 (July 2001 issue). Mount the CD and change to the RedHat/RPMS directory and issue the following command to install the Apache development RPM:

rpm -ivh apache-devel-1.3.19-5

Next, mount this month’s PCQuest CD and change to the directory cdrom/linux/mysql and copy the file php-4.0.6.tar.gz to /opt directory (say). Untar and uncompress the archive. The commands are:

mount /mnt/cdrom 



cd /mnt/cdrom/cdrom/linux/mysql


cp php-4.0.6.tar.gz /opt


cd /opt


tar -zxvf php-4.0.6.tar.gz

The last command will result in a directory named php-4.0.6 under /opt directory. Change to this directory (cd php-4.0.6) and issue the following commands:

./configure -–with-apxs 



-–with-mysql

make



make install

Restart the Apache Web server by:

/etc/rc.d/init.d/httpd restart

Setting up Perl



Install the RPM named mysql-devel-3.23.36-1.rpm found in RedHat/RPMS subdirectory on PCQ Linux CD 2. For setting up Perl for MySQL, install the RPMs ‘perl-DBI-1.14-10.rpm’ and ‘perl-DBD-MySQL-1.2215-1.rpm’, corresponding to the DBI libraries and the DBD libraries for MySQL. You can find the former RPM on PCQ Linux CD 1 and the latter on CD 2 in the RedHat/RPMS directory. Most likely the Perl interpreter would be preinstalled on your system. You can check this by issuing the command ‘rpm —qa | grep perl-5.6.0-12’. If you don’t get any output install the perl-5.6.0-12.rpm found on PCQ Linux CD 1. While installing these RPMs, if you get a ‘libz.so.1 dependency’ error use the —nodeps option as follows:

rpm —ivh  



-–nodeps

Setting up Tomcat

For setting up Tomcat and interfacing it with MySQL, you would need JDK and JDBC drivers for MySQL. You can find the JDK for Linux in the September 2001 PCQuest CD and the MySQL JDBC

drivers, named MM.MySQL, in the December 



2001 CD.

Mount the September 2001 CD and copy ‘j2sdk-1_4_0-beta-linux-i386-rpm.bin’ to /opt directory. The corresponding commands are:

mount /mnt/cdrom



cd /mnt/cdrom/cdrom/linux


cp j2sdk-1_4_0-beta-linux-i386-rpm.bin /opt

Change to /opt and execute this file as:

./j2sdk-1_4_0-beta-linux-i386-rpm.bin

Type ‘yes’ to agree to the license. This will create an RPM file called j2sdk-1.4.0.i386.rpm in the /opt directory. Install this RPM by:

rpm -ivh j2sdk-1.4.0.i386.rpm

This will install JDK in the directory /usr/java/j2sdk1.4.0

You must now set an environment variable named JAVA_HOME containing the path to the JDK directory. To do this, in the linux console issue the command:

export JAVA_HOME=/usr/java/j2sdk1.4.0

Also append this line to the file named ‘profile’ found in /etc directory. Now mount this month’s PCQ CD and from the directory cdrom/linux/mysql copy the file named

jakarta-tomcat-4.0.1.tar.gz to /opt directory, change to /opt and then issue the

comand:

tar -zxvf /opt/jakarta-tomcat-4.0.1.tar.gz

This will create a directory named jakarta-tomcat-4.0.1. Rename this directory–just to refer to it easily–as ‘tomcat’. In the last month’s article MySQL GUI Clients (page 126), in the section ‘Squirrel SQL Client’ we have explained how to install the MM.MySQL drivers. For the JSP to work, copy the file mm.mysql-2.0.7.jar to /opt/tomcat/lib directory.

Start the Tomcat engine by issuing the command:

/opt/tomcat/bin/startup.sh

Interfacing with MySQL



Let us now see how to use each of these scripting engines to interface with the MySQL database. Logically the following four steps are involved:

1. Connect to the database server by specifying the IP address of the machine running the database server with a login name and the corresponding password
2. Select a database to query
3. Issue a query
4. If the query returns data (like in case of SQL SELECT queries), iterate through it and display the data using a loop–while, for etc.

Now let’s see how these steps are performed in PHP, Perl and JSP. The entities between < >, like , , must be substituted with the appropriate values. In case of PHP and Perl the dollar ($) sign before an entity states that it is a variable.

With PHP

1. With PHP you can connect to the MySQL database server using the mysql_connect( ) function, whose syntax is as follows:
   
   
   
   $connection_ref = mysql_connect(“”,””,” ” );
   
   
   
   
   The functions return a reference (pointer) to the connection which must be stored in a variable–in our case we store it in the variable named $connection_ref–because it will be used in the subsequent steps.
   
2. Using the mysql_select_db( ) function, a database is selected.
   
   
   
   mysql_select_db(“”,$connection_ref);
3. Then we use mysql_query( ) function to issue a SQL query on the selected database.
   
   
   
   $query_result = mysql_query(“”,$connection_ref);
   
   
   
   
   In case of SQL SELECT queries, the mysql_query( ) function returns the resultant data. So, we must store the result in a variable–called $query_result in our case. Remember that the result returned is in a tabular form comprising rows and columns.
   
4. In case the query returns a result, we pick each row in the result and store it in an array using the mysql_fetch_array( ) function. The syntax of this function is:
   
   
   
   $row = mysql_fetch_array ($query_result);
   
   
   
   
   The first call to the mysql_fetch_array( ) function returns the first row in the result as an array. Using a ‘while’ loop we iterate through the rows. Within the ‘while’ loop, we iterate through the array elements–which are columns–using a ‘for’ loop.
   

With Perl

1. Connect to the database using connect( ) function of the DBI library whose syntax is:
   
   
   
   $connection_ref = DBI->connect(“DBI:mysql:: address-or-hostname>”,””,” ”);
2. The database is selected in the first step itself.
3. Two functions are used for this step, prepare( ) and execute( ), whose syntax are :
   
   
   
   $query = $connection_ref-> prepare(“”);
   
   
   $query->execute( );
   
   
   
   
   With the first statement the query to be issued is prepared and stored in the variable $query and with the next statement the query is actually executed.
4. Each row of data in the returned result (if any) is picked as an array using the fetchrow_array( ) function whose syntax is :
   
   @row = $query->fetchrow_array( )
   
   
   
   
   
   Note that Perl arrays are denoted by the suffix ‘@’. Here ‘@row’ represents an array. Subsequently, we can use a ‘while’ and a ‘for’ loop to iterate through and display the result.

With JSP

1. Prior to connecting to the database, the JDBC drivers must be loaded by the following statement:
   
   
   
   Class.forName(“”);
   
   
   
   
   In case of MM MySQL drivers, the is ‘org.gjt.mm.mysql.Driver’.
   
   
   
   Connection to the database is established using the java.sql.DriverManager.getConnection( ) function as follows:
   
   
   Connection connection_ref = DriverManager.getConnection(“jdbc:mysql:/// ”,””,” ”);
   
   
   The function returns a connection reference as an Object of class connection that is stored in the object variable ‘connection_ref’. For non-Java programmers this simply means that the connection reference is stored in the variable named
   connection_ref.
   
   
   
   
2. The database is selected as a part of the above statement.
3. Following two statements are used to query the database:
   
   
   
   Statement query = connection_ref.createStatement( );
   
   
   ResultSet query_result = query.executeQuery(“”);
   
   
   
   
   In case of SQL queries that modify the database like INSERT, DELETE, UPDATE, in place of executeQuery( ) function we use executeUpdate( ) function as:
   
   
   
   query.executeUpdate(“”);
4. The query_result can be thought of as an array (though it is not), which contains the rows of data returned in case of SELECT queries. Each row can be iterated using the next( ) function and each column within the row can be displayed using the getString( ) function.
   
   
   
   query_result.next( );
   
   
   query_result.getString(
   );
   
   

The

starts with 1.

On this month’s CD, in the subdirectory cdrom/sorc_cod/mysql you can find examples of PHP, Perl and JSP scripts that add and display data in a database. The file ‘install.txt’ tells you how to execute the scripts and see them working. You can open the scripts in a text editor to see their code.

Shekhar Govindarajan