Page Views with Ajax and PHP and MySQL
Forward: In this tutorial I explain how you can determine the page views using Ajax, PHP and MySQL.
By: Chrysanthus Date Published: 16 Feb 2013
Introduction
Strategy
Recall, Ajax is a web page client code; PHP 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 PHP script. The PHP script sends the information to the database. The PHP 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.php', 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 PHP 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 PHP Script
A simple PHP script to receive the data from the web page and send to the database is:
<?php
$articleID = $_POST['articleID'];
$articleURL = $_POST['articleURL'];
$link = mysql_connect('localhost', 'username', 'psswrd');
if (!$link)
{
echo "<h3>Connection could not be made! Check usernameand/or password, and try again.</h3>";
}
else
{
//select database
$db_selected = mysql_select_db('dbName', $link);
$readingStr = "insert into reading (articleID,articleURL, . . .) values (\"$articleID\", \"$articleURL\", . . .)";
$readingStResult = mysql_query($readingStr,$link);
mysql_close($link);
}
?>
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 PHP script that will send the feedback result to a web page at the client browser. In this tutorial 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 Articles
Simulating an HTML HyperlinkRedirecting with HTML and ECMAScript
Page Views with Ajax and PHP and MySQL
Image and Text as same Hyperlink
Major in Website Design
Web Development Course