Broad Network


Page Views with Ajax and Perl and MySQL

Ajax, Perl and MySQL

Web Development with Perl and MySQL

Foreword: In this tutorial I explain how you can determine the page views using Ajax, Perl and MySQL.

By: Chrysanthus Date Published: 6 Feb 2015

Introduction

Page Views is the number of times a web page has been viewed within a period. The period can be one day, one week, one month, etc. Total Page Views is the number of times the number of pages of interest, in a website, has been viewed within a period. In this tutorial I explain how you can determine the page views using Ajax, Perl and MySQL. Page views is sometimes written as, Pageviews. You need basic knowledge in Ajax and Perl and MySQL in order to understand this tutorial.

In this tutorial, you use the PurePerl MySQL API, which is very easy to install and very easy to use. To search and download it, just use the search box (above) of this page.

Strategy
Recall, Ajax is a web page client code; Perl is a server script (language) and MySQL is a server database. For each web page of interest in the website, you place in an Ajax code. Each time a page loads into a browser at the client, the Ajax code is executed. When a page loads, it is assumed that the page has been read or viewed. The Ajax code sends this information to the server. At the server, the information is received by a Perl script. The Perl script sends the information to the database. The Perl script and database may be in one computer.

Ajax Code
This is a simple Ajax code. It assumes that the web page is an article. So an article ID of 10 is coded into the page. The URL of the page is determined in the page, when it loads. This Ajax code sends the article ID and web page URL as HTTP POST variables of articleID and articleURL to the server. Read the code:

    <script type='text/ECMAScript'>

        articleID = "10";
        var currentURL = document.URL;

        //ajax

        var xmlhttp;

        if (window.XMLHttpRequest)
            {// code for IE7+, Firefox, Chrome, Opera, Safari
                xmlhttp = new XMLHttpRequest();
            }
        else
            {// code for IE6, IE5
                xmlhttp = new ActiveXObject('Microsoft.XMLHTTP');
            }

        xmlhttp.onreadystatechange=function()
         {
            if (xmlhttp.readyState == 4)
             {
                 if (xmlhttp.status == 200)
                    {

                    }
                 else
                    {

                    }
             }
         }

        sendStr = 'articleID='+articleID+'&articleURL='+currentURL;

        xmlhttp.open('post', 'http://www.somesite.com/pageViews.pl', true);
        xmlhttp.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
        xmlhttp.send(sendStr);

    </script>

As you can see, the above Ajax code is executed each time the page loads at the browser. Each page has its own unique articleID. Of course each page has its own URL.

The Database
In the database, there is a table called, reading. The table notation of reading is:

    reading(readingID, articleID, articleURL, . . .)

The ellipsis (. . .)  means there are other columns. So, each time a web page loads, the page’s Ajax code is executed and a row is inserted into the reading table, with the pages articleID and URL and other data such as date or timestamp. It is the Perl script that inserts the row. Timestamp datum indicates the date and time when the page was viewed (actually exactly when the row was inserted).

The Perl Script
A simple Perl script to receive the data from the web page and send to the database is:

use Mysql;
use CGI;
use strict;

print "Content-Type: text/html\n\n";

    my $obj = CGI->new();

    my $articleID = $obj->param('articleID');
    my $articleURL = $obj->param('articleURL');

        if (!Mysql::connect("username", "psswrd", "localhost", 3306))
            {
                print "<body style='background-color:bisque'><h3>$Mysql::Error_msg</h3>";
            }
        else
            {
                #select database
                    if (!Mysql::select_db("dbName"))
                    {
                        print "<body style='background-color:bisque'><h3>$Mysql::Error_msg</h3>";
                    }
                else
                    {
                        my $readingStr = "insert into reading (articleID,articleURL, . . .) values (\"$articleID\", \"$articleURL\", . . .)";
                        if (!Mysql::query($readingStr))
                            {
                                print $Mysql::Error_msg, "<br>";
                            }
                    }
                Mysql::close();
            }

Again, ellipsis means more columns. It receives the articleID and URL by the POST method. It then makes the connection with the database server. After that it selects (chooses) the database having the table, reading (there can be more than one database for a MySQL server). It goes on to prepare the query (INSERT) string; then it executes the query. Of course it finally closes the connection.

Knowing the Page Views
All the above information explains how to record the page views in a database. To know the page views, you issue a MySQL SELECT query to the database. You can do this directly using SQL code (programming). You can also do it with a Perl script that will send the feedback result to a web page at the client browser. In this section I only explain how you can use SQL SELECT queries to obtain particular information on page views.

If you want to see all the rows (recorded data) of the reading table, you would execute the query:

    select * from reading;

However, this is not recommended for an active website because the rows would be too many and would take a long time to be displayed, completely.

If you want to know the total number of page views ever, for all the web pages, you would execute a SQL statement like:

    select count(*) AS OverallNo from reading;

If you want to know the total number of times the web page with article ID, 10 has been viewed ever, you would execute something like:

    select count(*) AS articleID10 from reading where articleID = 10;

If you want to know the total number of page views (of all pages) from one timestamp to another timestamp, assuming that there is a column called timestamp, you would execute something like:

    select count(*) AS NoTimeStp from reading where timestamp < "2013-02-15 23:59:59" AND timestamp > "2012-11-01 23:59:59";

If you want to know the number of page views for a particular web page between two timestamps. You would execute something like:

    select count(*) AS articleID10 from reading where articleID = 10 AND timestamp < "2013-02-15 23:59:59" AND timestamp > "2012-11-01 23:59:59";

If you want to know the total number of page views for each web page between two timestamps, you would type something like:

    select count(articleID) AS NoTimeStp, articleID from reading where timestamp < "2013-02-15 23:59:59" AND timestamp > "2012-11-01 23:59:59" GROUP BY articleID;

Your result in this case would be a list, where each row is for a particular article.

I have explained to you how to determine page views under different conditions. In your commercial application, you would need more code than all what I have given above; however, I have given you the main points.

Thanks.

Chrys

Related Links

Web Development Basics with Perl and MySQL
Perl Validation of HTML Form Data
Page Views with Ajax and Perl and MySQL
Search Within a Site with Perl and MySQL
Web Live Text Chart Application with Perl and MySQL
More Related Links
PurePerl MySQL API - Free Download
Major in Website Design
Perl Course

Comments

Become the Writer's Fan
Send the Writer a Message