10.4.4.1 Web Page Counters Using a MySQL Database

Submitted by admin on Wed, 2007-11-14 10:21

10.4.4.1 Web Page Counters Using a MySQL Database

In Section 10.2.3, we discuss how Web page counters can be kept in a DBM file. A DBM file stores a set of key-value pairs. A key corresponds to the name of a counter and the value corresponds to the current value of the counter. A counter is referenced by name in a Web page. Actually, the counter’s name is passed as an argument to a CGI program. The CGI program accesses the DBM file, obtains the current value of the named counter, creates a graphic image using the GD.pm module, sends it as an in-line image to the Web page that is displayed by the Web browser in the appropriate location on the Web page. Of course, a database table can be used to store counter names and their corresponding values. A very simple two or three-column table that
uses the unique counter name or a unique numeric counter ID as the primary key can accomplish this requirement. In the context of databases, the representational and storage needs are very simple. However, if a company, an organization or an individual, wants to provide a service that allows any individual creating a Web page anywhere in the world maintain counter values for free or for a cost, such as thecounter.com, using a database is very appropriate. The single table used in the example given below can be used together with other related tables in a database to store information about the Web pages, the subscribers of the counter service’s facilities, the access statistics for a page, etc. Thus, the table discussed below can be a component of a much more complex and sophisticated system.

The following interaction with a MySQL database gives the definition of the table used. It also shows that the initial value of the counter DBBasedCounter1 is zero. This is an illustrative example, and we assume that there is only one counter in the table called COUNTERS. In a sophisticated, possibly commercial system, such a table may contain millions of entries. We create a MySQL table using the following SQL statement typed in from the command-line at a MySQL client. The database user is Project.

mysql> create table COUNTERS (

COUNTER_ID INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,

COUNTER_NAME CHAR (50) NOT NULL UNIQUE,

COUNTER_VALUE INT(10) UNSIGNED NOT NULL);

Query OK, 0 rows affected (0.00 sec)

mysql> describe COUNTERS;

+---------------+------------------+------+-----+---------+----------------+---------------------------------+

+---------------+------------------+------+-----+---------+----------------+---------------------------------+

+---------------+------------------+------+-----+---------+----------------+---------------------------------+

3 rows in set (0.00 sec)

mysql> select * from COUNTERS;

+------------+-----------------+---------------+

| COUNTER_ID | COUNTER_NAME | COUNTER_VALUE |

+------------+-----------------+---------------+

| 1 | DBBasedCounter1 | 0 |

+------------+-----------------+---------------+

1 row in set (0.00 sec)

The table COUNTER is created by typing the appropriate SQL in a MySQL client. In a Linux machine, the MySQL client usually has a command-line interface and is invoked by typing mysql as a command. The table has three columns: COUNTER_ID, COUNTER_NAME, and COUNTER_VALUE. COUNTER_ID is an integer that can be up to six decimal digits in length. It cannot be empty in any row. It is the primary key, and hence unique across all the rows of the table. The value of COUNTER_ID is automatically incremented by 1 for every new row (or counter) entered in the table. COUNTER_NAME is a character string of length up to 60. A counter must have a name; thus, COUNTER_NAME cannot be empty. In addition, COUNTER_NAME is unique across all rows in the table. The third column is COUNTER_VALUE. It is an unsigned integer up to 10 digits in length. Its value cannot be empty. The default initial value of this field is zero. A counter called DBBasedCounter1 is created an an appropriate row entered into the table. This is an illustrative situation, and the table contains only one row.

Once we have the COUNTER table, and the counter named DBBasedCounter1, we can use it in a Web page to produce a GD-based graphical access counter. The program that follows is similar to the one that we discuss in Section 10.2.3 except that it uses a database to store counter values instead of a DBM file.

Program 10.13

#!/usr/bin/perl -T
#counterDB.pl

use GD;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use Untaint; 
use DBI;

#######Set CGI size limit; disable file upload
$CGI::POST_MAX = 1024; #max 1024 bytes posts
$CGI::DISABLE_UPLOADS = 1; 

#database details
my ($driver, $database,  $user, $password);
my ($dsn, $dbh,, $table,  $sth, $DBQuery);
my ($CGIQuery, $name_counter, $counter,  @digits, $length, $im);
my ($black, $white, $blue, $i, $counter_value);
$driver = "mysql";
$database = "test";
$dsn = "DBI:$driver:$database";
$user = "Project";
$password = "Project";

#CGI details
$CGIQuery = new CGI;
$name_counter = $CGIQuery->param(counter);
$name_counter = untaint(qr{^[\d\w.-]+$}, $name_counter);

##need to start the connection 
$dbh = DBI->connect ($dsn, $user, $password) or
    die "Cannot connect to $dsn: $DBI::errstr";

#prepare a query; 
$table = "COUNTERS";
$DBQuery = qq{select COUNTER_VALUE from $table
               WHERE COUNTER_NAME = "$name_counter"};
$sth = $dbh -> prepare ($DBQuery);
$sth -> execute () or warn $dbh -> errstr;
my $row = $sth -> fetchrow_hashref();
$counter_value = $row -> {'COUNTER_VALUE'};
$sth -> finish();

$counter_value  = untaint(qr{^\d+$}, $counter_value);
#Increment the retrieved counter value and write it back to the database
$counter_value++;
$DBQuery = qq{update COUNTERS set COUNTER_VALUE = $counter_value
                  where COUNTER_NAME = "$name_counter"};
$sth = $dbh -> prepare ($DBQuery);
$sth -> execute () or warn $dbh -> errstr;
$sth -> finish ();
$dbh -> disconnect ();

#Find the number of digits in the counter
@digits = split(//, $counter_value);
$length = length($counter_value);
#Create a new GD image
$im = new GD::Image($length*14-2,20);

#Create colors by allocating RGB
$black = $im->colorAllocate(0, 0, 0);
$white = $im->colorAllocate(255, 255, 255);        
$blue = $im->colorAllocate(0,0,255);
#Create the GD image containing the counter's value
srand();
for ($i=0; $i < $length; $i++)
{ $im->string(gdLargeFont, 2+$i*14, 1+int(rand(3)), $digits[$i], $white);
  if($i < $length-1)
  { $im->filledRectangle(12+$i*14, 0, 13+$i*14, 20, $blue);
  }
}


# print the image to STDOUT, i.e., send to browser
print header("image/jpeg");
binmode STDOUT;
print $im->jpeg;

exit;

The program uses the GD.pm module to create a graphic image of a counter, the CGI.pm module to handle CGI queries, and the DBI.pm module to interact with a database. The CGI::Carp.pm module is used to get notification of CGI errors. The CGI errors are printed to the browser for purposes of easy debugging. The program used a MySQL database server. Therefore, the DBD driver module used is mysql.pm.

The program connects to the MySQL database running on the same machine as the CGI program. The database used is test. The database requires a valid user name and password combination. The program creates a new CGI object called $CGIQuery to handle the current query or request coming from a Web browser. It obtains the parameter named counter and calls the value of this parameter $name_counter. We have used an object-oriented interface to CGI.pm in this program.

$CGIQuery = new CGI;

$name_counter = $CGIQuery->param(counter);

The value is untainted or laundered. The program follows by attempting to make a connection to the specified database source using the user name-password combination given.

$dbh = DBI->connect ($dsn, $user, $password) or

die "Cannot connect to $dsn: $DBI::errstr";

The program next writes up a database query and quotes it appropriately using qq. qq allows variable interpolation, and the string inside can span more than one line. The SQL query is given below.

select COUNTER_VALUE from $table

WHERE COUNTER_NAME = "$name_counter"

As usual, the query is prepared before execution. The prepare method of the database handle object $dbh takes the database query string as argument and returns a statement handle $sth which is used later to execute the query and fetch the result of execution of the query.

$sth = $dbh -> prepare ($DBQuery);

$sth -> execute () or warn $dbh -> errstr;

Once a database query has been successfully executed by the database, the results are returned by the database server to the Perl program. The returned results can be accessed in several ways from within the Perl program. This program shows one way, using fetchrow_hashref. The name of a counter is unique in the database table COUNTER, and therefore, only one row is returned as a response to the successful execution of the database query. The row can be accessed within the Perl program as an array, or as a hash, or as a reference to an array or a hash. fetchrow_hashref returns a reference to this row where the contents of the row itself is a hash. In such a hash, a field or column name is a key, and the
content of the field is the value. fetchrow_arrayref, which is not used in this program, would have returned a reference to the contents of the row, where the row itself is considered to be returned in the from of an array. Similarly, fetchrow_array returns the contents of the row as a regular array. Any one of these three methods can be used to obtain the contents of one row, and which one is used depends on the programmer’s preference. Obtaining a reference, and dereferencing takes time. However, if the contents of a row are fairly voluminous, it makes sense to use references instead of dealing with the contents directly. Using a hash requires additional time compared to obtaining a row directly as an array. When a row is accessed as an array, one needs to know the sequence of field names as they are returned. However, when a row is accessed as a hash, the fields are
accessible using the field names as keys. Thus, there are certain decisions to be made regarding how the data in a row is accessed.

If a database query returns several rows, fetchrow_array, fetchrow_arrayref or fetchrow_hashref returns information about the next row of results. If there is no row left, each one of these methods returns undef. Thus, when more than one row is returned, the fetching of all rows of results requires the use of a while loop with the fetching method as a conditional. The program discussed in Section 10.4.4.2 illustrates the use of such a loop. In the current program, we know that the result contains only one row, and hence, performing fetchrow_hashref once is enough. The value of the
counter is obtained from the hash.

my $row = $sth -> fetchrow_hashref();

$counter_value = $row -> {'COUNTER_VALUE'};

$sth -> finish();

When the results associated with a successfully executed database query or statement are extensive, and they are read sequentially, it makes sense to use the finish method to clean things up at the end before the next query is issued. In the current program, it is not really necessary to use the finish method.

Next, the value of the counter obtained from the database is incremented. This reflects the fact that our program is a CGI program that records accessed to a Web page. Every time the CGI program is executed, there is an additional access to the associated page. The database also needs to reflect the incremented value of the counter. The appropriate database query is given below.

update COUNTERS set COUNTER_VALUE = $counter_value

where COUNTER_NAME = "$name_counter"

The query is prepared using the database handle $dbh and a statement handle $sth obtained. The statement is executed. If it is executed successfully, the new value of the counter is available in the database. The statement is finished, and the connection to the database killed.

At this point in the program, it has the value of the access counter it needs to display graphically on the Web page as an in-line image. The value is converted into a JPEG image that is sent back to the browser with an appropriate HTTP header. The manner in which the image is created has been discussed in Section 10.2.3 and Section 5.7. A Web page where the CGI program discussed above is used is shown in Figure 10.25. The HTML that produces the counter image on the Web page is given below.

<html>

<head>

<title> Jugal Kalita </title>

</head>

<body>

<h2> Jugal Kalita </h2>

<h3> University of Colorado at Colorado Springs </h3>

Number of visitors (<em>since March 4, 2002</em>):

<IMG SRC=

"http://pikespeak.uccs.edu/cgi-bin/kalita/counter/counterDB.pl?counter=DBBasedCo

unter1">

<p>

</body>

</html>

Figure 10.25: A Counter Image Produced by a CGI Program Using GD.pm in a Web Page; The Counter is Stored in a Database Table

Add new comment

On Perl

10.4.4.1 Web Page Counters Using a MySQL Database

10.4.4.1 Web Page Counters Using a MySQL Database