Count clicks the right way! in PHP – A Simple Page Click Count System Using PHP And MySQL
Step 1: Set Up Your MySQL Server And PHP Pages

In order to track clicks on a Web page, you need to use a server-side scripting language (e.g., PHP) and a server-side data store (e.g., MySQL). Fortunately, most Web hosts offer both.

Most Web hosts also use cPanel, or something very similar, to let you set up your Web site’s services, including MySQL. And for this exercise, you’re going to need a MySQL database, so it’s time to go ahead and set one up if you don’t already have one.

 

Your Web host should provide you with a MySQL server name, user name and password. You’ll need this information to do this project. This login information may be the same as your FTP login information, but it’s probably different. Again, check with your Web host.

You need to create a database on the MySQL server, if you don’t already have one. Your Web host may have already done this for you; check with them. If not, cPanel (or whatever interface you were given to manage your site) should have a way for you to create a database. Again, check with your host if you need help with this, as all servers are different.

A final note: In addition to needing to set up a MySQL database for your site, you need to make any page you wish to count a PHP page. HTML pages will not work.

Usually, this is as simple as changing your pages’ file extensions from .htm / .html to .php. In other words, if your home page is named index.html, changing its name to index.php should be enough to get it to work as a PHP page.

Again, you need to change the file extensions of every page you want counted to be .php.

Step 2: Create The MySQL Table

Once you have changed all your file extensions and created the MySQL database you will use, you need to add a table to the database. This table will actually store the page counts.

If your Web host offers MySQL, it almost certainly offers phpMyAdmin, which is a Web interface to manage your database. If you have cPanel, phpMyAdmin is probably located there; otherwise, check with your Web host for how to access phpMyAdmin.

Open up phpMyAdmin, select the database you just created from the menu on the left, click the SQL tab, paste the code below into the box, and click the Go button.

CREATE TABLE click_count (

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     page_url VARCHAR(255) NOT NULL,
     page_count INT UNSIGNED NOT NULL,
);

After phpMyAdmin reports the table has been created, you should see it appear on the left, under your database name. We’re done; you can close out phpMyAdmin and move back into Dreamweaver for Step 3.

Step 3: Add The PHP Code

A page click counter is a fairly straightforward programming problem:

  • Check to see if this is the first visit by this person to this page. We don’t want to increment the current page’s click counter if the user refreshes the page, we only want to increment it if someone comes from someplace else.
  • If this isn’t a refresh, check the database to see if there is a click count for the current page.
  • If there is no click count for the current page create one for this page and set its count to 1.
  • If there is a click count for the current page, get the current count, add one, set that to be the new click count for the page and update the database.

We’ll accomplish this with the following PHP code, which should appear at the very top of each page you want counted, before the DOCTYPE declaration:

<?php

//MySQL database variables
//You need to change these variables to be right for your MySQL install
$host = "localhost";
$user = "user";
$pass = "pass";
$dbname = "databasename";
//DO NOT CHANGE THE FOLLOWING CODE!
//start a PHP session
//this prevents spamming the click count by refreshing the page
session_start();
//create current page constant
$curPage = $_SERVER['PHP_SELF'];
//set number of clicks variable to 0
$clicks = 0;
//do not recount if page currently loaded
if($_SESSION['page'] != $curPage) {
     //set current page as session variable
     $_SESSION['page'] = $curPage;
     //try to connect to MySQL server
     if(!$link = mysql_connect($host, $user, $pass)) {
          echo "Could not connect to MySQL server. Check your login information; the MySQL server may also be offline or temporarily overloaded.";
     }
     //try to select database
     elseif(!mysql_select_db($dbname)) {
          echo "Cannot select database.";
     }
     else {
          //get current click count for page from database;
          //output error message on failure
          if(!$rs = mysql_query("SELECT * FROM click_count WHERE page_url = '$curPage'")) {
               echo "Could not parse click counting query.";
          }
          //if no record for this page found,
          elseif(mysql_num_rows($rs) == 0) {
               //try to create new record and set count for new page to 1;
               //output error message if problem encountered
               if(!$rs = mysql_query("INSERT INTO click_count (page_url, page_count) VALUES ('$curPage', 1)")) {
                    echo "Could not create new click counter for this page.";
               }
               else {
                    $clicks = 1;
               }
          }
          else {
               //get number of clicks for page and add 1
               $row = mysql_fetch_array($rs);
               $clicks = $row['page_count'] + 1;
               //update click count in database;
               //report error if not updated
               if(!$rs = mysql_query("UPDATE click_count SET page_count = $clicks WHERE page_url = '$curPage'")) {
                    echo "Could not save new click count for this page.";
               }
          }
     }
}
?>
Step 4: Displaying The Count

When the code above executes properly, it creates a variable named $clicks which contains the current page click count.

We can display this count anywhere we like on the page, by calling a simple echo command:

<?php echo $clicks; ?>

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s