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 Sep 2016
Introduction
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 (or google.com or yahoo.com).
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.
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).
Limiting SQL Injection
To limit SQL injection, use prepared statement and validation of articleID and articleURL at the server. The prepared and execute statements with validation code, is:
#prepare
my $prepareStr = "INSERT INTO reading (articleID, articleURL, . . .) VALUES (?, ?, . . .)";
if (Mysql::prepare($prepareStr) != 1)
{
print $Mysql::Error_msg, "\n";
}
#validate
sub isWholeNumber
{
if ($_[0] =~ /^\d+\z/)
{
return 1
}
else
{
return 0;
}
}
isWholeNumber($articleID) || die "Not an articleID, with reading!";
sub isURL
{
if ($_[0] =~ /^http|https:\/\/([0-9a-zA-Z_\-]{1,64}\.)?[0-9a-zA-Z_\-]{1,64}(\.[0-9a-zA-Z_\-]{2,4}){0,2}(:[0-9]{1,5})?(\/[0-9a-zA-Z_\-]{1,64}){0,64}([0-9a-zA-Z_\-]{1,64}(\.[a-zA-Z]{1,4})?)?(#[0-9a-zA-Z_\-]{1,64})?/)
{
return 1;
}
else
{
return 0;
}
}
isURL($articleURL) || die "Not a URL, with reading!";
#execute
my $executeStr = "$articleID, $articleURL";
if (Mysql::execute($executeStr) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print "statement executed \n";
}
Mysql::stmt_close;
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 $Mysql::Error_msg;
}
else
{
#select database
if (!Mysql::select_db("dbName"))
{
print $Mysql::Error_msg;
}
else
{
#prepare
my $prepareStr = "INSERT INTO reading (articleID, articleURL, . . .) VALUES (?, ?, . . .)";
if (Mysql::prepare($prepareStr) != 1)
{
print $Mysql::Error_msg, "\n";
}
#validate
sub isWholeNumber
{
if ($_[0] =~ /^\d+\z/)
{
return 1
}
else
{
return 0;
}
}
isWholeNumber($articleID) || die "Not an articleID, with reading!";
sub isURL
{
if ($_[0] =~ /^http|https:\/\/([0-9a-zA-Z_\-]{1,64}\.)?[0-9a-zA-Z_\-]{1,64}(\.[0-9a-zA-Z_\-]{2,4}){0,2}(:[0-9]{1,5})?(\/[0-9a-zA-Z_\-]{1,64}){0,64}([0-9a-zA-Z_\-]{1,64}(\.[a-zA-Z]{1,4})?)?(#[0-9a-zA-Z_\-]{1,64})?/)
{
return 1;
}
else
{
return 0;
}
}
isURL($articleURL) || die "Not a URL, with reading!";
#execute
my $executeStr = "$articleID, $articleURL";
if (Mysql::execute($executeStr) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print "statement executed \n";
}
Mysql::stmt_close;
}
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.
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.
Origin Problem
You need to be able to determine whether a hacker has not just written his own Ajax code and be filling your database when no web page has been downloaded; you also need to determine if the data to the database is from a web page downloaded - see later.
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 MySQLPerl Validation of HTML Form Data
Page Views with Ajax and Perl and MySQL
Web Live Text Chart Application using Perl and MySQL
Search Within a Site using Perl and MySQL
More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course