Broad Network


Page Views with Ajax and ECMAScript and MySQL

Ajax, ECMAScript and MySQL

Web Development with ECMAScript and MySQL in Node.js

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

By: Chrysanthus Date Published: 12 Oct 2016

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, ECMAScript and MySQL. Page views is sometimes written as, Pageviews. You need basic knowledge in Ajax and ECMAScript and MySQL in order to understand this tutorial.

In this tutorial, you use the Pure ECMAScript MySQL API (EMySQL), 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).

Strategy
Recall, Ajax is a web page client code; ECMAScript (in Node.js) 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 an ECMAScript script. The ECMAScript script sends the information to the database. The ECMAScript 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://localhost:8080/include/pageViews.js', 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 ECMAScript 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
    var prepareStr = `INSERT INTO reading (articleID, articleURL, . . .) VALUES (?, ?, . . .)`;
    con.prepare(prepareStr, function(err)
        {
            if (err)
                callback(err);
        });

    //validate - whole number
    ret = Number.isInteger(articleID);
    if (ret == false)
        {
            callback("articleID is not a whole number!");
        }

    function isURL(url)
        {
            if (url.search(/^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})?.*/) != -1)
                {
                    return true;
                }
            else
                {
                    return false;
                }
        }

    if (isURL(articleURL) == false)
        {
            callback("The URL is not correct!");
        }

    //execute
    var executeStr = `${articleID}, ${articleURL}`;
    con.execute(executeStr, function(err)
        {
            if (err)
                console.log(err);
        });

   con.statementClose();

The ECMAScript Script
In my computer, the node server is in the directory, c:/server. The pageViews.js file with the ECMAScript is in the directory, c:/server/include. A simple ECMAScript to receive the data from the web page and send to the database is:

const mysql = require('../Mysql.js');

exports.pageViews = function(bodyArr, callback)
    {
        body = Buffer.concat(bodyArr).toString();
        body = decodeURIComponent(body);
        body = body.replace(/\+/g, " ");
        body = body.replace(/^\s*/, "");
        body = body.replace(/\s*$/g, "");

        keyValueArr = body.split('&');

        keyValueMap = new Map();
        for (item of keyValueArr )
            {
                arr = item.split('=');
                keyValueMap.set(arr[0], arr[1]);
            }

        articleID  = keyValueMap.get('articleID');
        articleURL = keyValueMap.get('articleURL');


        con = new mysql.Connection("username", "secret", "localhost", 3306, function(err)
            {
                if (err)
                    callback(err[0].get('info'));
            });

        var db = `dbname`;
        con.selectDB(db, function(err)
            {
                if (err)
                    callback(err[0].get('info'));
            });

        //prepare
        var prepareStr = `INSERT INTO reading (articleID, articleURL, readerID, writerID, partNo) VALUES (?, ?, 3, 3, 3)`;
        con.prepare(prepareStr, function(err)
            {
                if (err)
                    callback(err);
            });

        //validate - whole number
        ret = Number.isInteger(articleID);
        if (ret == false)
            {
                callback("articleID is not a whole number!");
            }

        function isURL(url)
            {
                if (url.search(/^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})?.*/) != -1)
                    {
                        return true;
                    }
                else
                    {
                        return false;
                    }
            }

        if (isURL(articleURL) == false)
            {
                callback("The URL is not correct!");
            }

        //execute
        var executeStr = `${articleID}, ${articleURL}`;
        con.execute(executeStr, function(err)
            {
                if (err)
                    console.log(err);
            });

        con.statementClose();

        con.close();

    }

The program receives the articleID and the downloading page 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 ECMAScript 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.

Node Server Code
The node server code is:

    const http = require('http');
    const fs = require('fs');
    const pV = require('./include/pageViews.js');

    http.createServer(function(request, response)
        {
            var url = request.url;

            if (url == '/')
                url = '/server/index.htm';
            else
              url = '/server' + url;


            response.setHeader('Content-Type', 'text/html');


            if (url.search(/.*\.htm\s*$|.*\.html\s*$/i) != -1)
                {
                     fs.readFile(url, (err, data) => {
                         if (err) throw err;

                         output = data.toString('utf8');
                         response.end(output);
                    });
                }


            var body = [];

            request.on('data', function(chunk)
                {
                    body.push(chunk);      
                }).on('end', function()
                {

                    if (url.search(/include\/pageViews\.js/i) != -1)
                        pV.pageViews(body, function(err, feedback)
                            {
                                if (err)
                                    {
                                         response.end(err);
                                    }
                                 else
                                    {
                                         response.end(feedback);
                                    }
                            });

                    //images
                    if (url.search(/.*\.gif\s*$|.*\.jpg\s*$/i) != -1)
                        {
                             fs.readFile(url, (err, data) => {
                                     if (err)
                                         throw err;
                                     else
                                         {
                                             matchObj = url.match(/\....\s*$/);
                                             matchStr = matchObj.toString();
                                             ext = matchStr.replace(/\s*/g, '');
                                             ext = ext.replace(/\./, '');
                                             contType = 'image/' + ext;  
                                             response.setHeader('Content-Type', contType);
                                             response.end(data);
                                         }
                                });
                        }

                });


            request.on('error', function(err)
                {
                    // print error to output
                });

            response.on('error', function(err)
                {
                    //send error message to output
                });

        }).listen(8080);

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 (reading table) when no web page has been downloaded; you also need to determine if the data to the database is from a web page downloaded.

The reading table (notation) could have been:

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

where articleID and articleURL are sent by the client browser to the database and articleIDServ and articleURLServ, supposedly the same values as articleID and articleURL respectively, are sent to the database by the node server when it is sending the downloaded web page to the client.

So, when the web page in question, is downloaded from the server to the client, the node server file (program) inserts the values for articleIDServ and articleURLServ. The ajax from the client will instead update the same row in the reading table with articleID and articleURL. You can go on to write code that will compare articleID with articleIDServ and articleURL with articleURLServ, to see if they are the same.

I have not provided any code for the origin problem. The above server code does not solve the origin problem; neither does the previous ECMAScript contributes to the solution (does not uptade).

I have explained to you how to determine page views.

Thanks.

Chrys

Related Links

Web Development Basics with ECMAScript (JavaScript) and MySQL
ECMAScript (JavaScript) Validation of HTML Form Data
Web Live Text Chart Application using ECMAScript (JavaScript) and MySQL
Page Views with Ajax and ECMAScript (JavaScript) and MySQL
Search Within a Site using ECMAScript and MySQL
More Related Links
Node Mailsend with JavaScript
EMySQL API with JavaScript
Node.js Web Development Course with JavaScript
Major in Website Design
Low Level Programming - Writing ECMAScript (JavaScript) Module
ECMAScript (JavaScript) Course

Comments

Become the Writer's Follower
Send the Writer a Message