Lesson 12 - Object-oriented hit counter in PHP and PDO

In the previous exercise, Solved tasks for OOP in PHP lessons 9-11, we've practiced our knowledge from previous lessons.

In previous lesson, Solved tasks for OOP in PHP lessons 9-11, we have finished up with static members. As promised, we'll get some practical stuff done today. We will be working with a database using the object-oriented PDO driver.

I was on teaching you all how to make surveys, but we've already got a non-OOP tutorial on that, which I'm sure you'd be able to re-create in object-oriented form. Instead, what we'll do it program a website visitor counter.

Hit counting methods

When we count website visits (sometimes referred to as "hits", which isn't accurate), we usually distinguish the following terms:

1. Pageviews - The total number of pages that visitor views. Every single page access is counted. If a user opens 10 subpages in an hour, 10 pageviews are be counted.
2. Visits - A visitor will only be counted once every 30 minutes. If a user opens 10 subpages in an hour, it will count as 2 visits.
3. UIP - The number of unique IP addresses in a given amount of time. If a user reads 1000 articles in a month, he/she would still only count as one UIP.

Page views are the highest count and UIP is the lowest. However, UIP has the highest informative value. Visits are something in-between, so we won't count them this time because it's not quite easy to distinguish them. We will only store page views and compute the UIP by selecting the unique page views in a given amount of time.

Database

We will store page views in a database. With every page view, we will add a row to the database with the current date and the visitor's IP address. We could store other information like their web browser or language too, but we'll leave all of that out for now.

Popular websites don't add page views to their database with every single access because doing so would cause performance issues. Pageviews are logged into a text file and once in a while, a script is executed which stores them into the database in a single batch. This way, the database isn't accessed as frequently, which results in a much faster load. To keep it simple, we won't do any of that and will simply store page views directly into the database.

Creation scripts

Let's create a table and name it pageviews, it will consist of three columns:

• pageviews_id (int, AI, primary key)
• ip (varchar 40)
• created (datetime)

You may use PHPMyAdmin to create the table if you want:

Eventually, you'll be able to use creation scripts like these:

CREATE TABLE IF NOT EXISTS `pageview` (
  `pageview_id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(40) COLLATE utf8_general_ci NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`pageview_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1;

Now let's get to the good stuff!

Introducing PDO

In the previous lesson, I went over how we almost always use the object-oriented PDO driver when working with databases in PHP. PDO is a class as well as a part of the PHP language. Let's connect to the database and insert a new row into the pageview table using PDO.

Connecting

We'll start by creating a brand new project in a new folder named webcounter where you will add an index.php file. Then, add the following lines to index.php:

<?php

$settings = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);

$connection = new PDO("mysql:host=localhost;dbname=webcounter_db", 'root', '', $settings);

First of all, I created an array and stored the desired PDO settings. The first key states the way we want errors to be handled, which I set to exceptions (we'll get to them later on in the course). The second one says that we don't want PDO to emulate prepared statements, which is needed for older MySQL versions that couldn't do it themselves. Without this setting, we wouldn't be able to use parameters in the LIMIT clause. The third and final key specifies the initialization command for the database, which I set to allow UTF-8 encoding.

The key is used to create a connection, which I did by creating a PDO instance. The first parameter in the constructor is a connection string with parameters. The first parameter is the host (localhost), the second one is the database's name (which in my case is webcounter_db). The next two constructor parameters are the username (root) and the password (which in my case is empty). The final parameter is where we pass our settings. If you don't get errors after adding this line, that means that you have successfully connected to the database. Otherwise, check your login credentials.

Queries

Now that we have a PDO connection instance, we can execute database queries. Aside from outdated drivers, PDO also supports what are referred to as prepared statements. That is, separating a query from the parameters it uses. When a query and its parameters are separated, SQL injection is negated and impossible. The database will handle the received parameters as a separate set of instructions. In outdated versions of SQL, functions like mysql_real_es­cape_string() were used to provide this same kind of protection, but using them nowadays is irrelevant

Now let's write a few more lines into index.php:

$query = $connection->prepare('INSERT INTO `pageview` (`ip`, `created`) VALUES (?, NOW())');
$parameters= array($_SERVER['REMOTE_ADDR']);
$query->execute($parameters);

The first thing I did here, is create a query instance, which is returned by the prepare() method that we call on the PDO connection instance. As method parameters, we simply add an SQL query as a string. Use question marks instead of query parameters! We never insert variables or values directly into a query! Doing so is a huge security risk.

Once that is done, we insert the parameters into a new array, if there is more than one, keep the same order as the question marks in the actual query. $_SERVER['REMO­TE_ADDR'] includes the local IP address (on a local computer it may be a value of 1).

Then we run the query using execute() method, to which we pass the array with the required parameters. Once you've done all of that, go back to PHPMyAdmin and check the table, you will find that a new row has been added.

IMPORTANT! If PHP error reporting is turned on and you have an error in your query, the error message will be displayed. PDO driver throws exceptions when errors are caught. Since they're printed along with the stack trace, your database password may be displayed along with part of the source code! This is why you must have error reporting turned off on the production server!

That will be all for today. In the next lesson, Object-oriented hit counter in PHP - continuation, we'll continue working on this short project by creating a new object that stores visits, analyzes them, and prints them out. Today's lesson code along with the DB export can be found in the lesson attachment.

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.