A Simple Page Click Count System Using PHP And MySQL

Recently asked on Yahoo! Answers:

Click Counter in Dreamweaver?
I am looking to create a hit counter so I can have the top ten clicked stories on my website. I have a separate field for the count but i need the code to update the field +1.

This isn’t something you can do with Dreamweaver’s built-in behaviors; you’re going to have to do this yourself, by hand. Fortunately, a page click counter is fairly easy to create in PHP and MySQL, so let’s do it, shall we?

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; ?>

For example, if you want to indicate how many times a page has been clicked as a little bit of italicized text, you would simply edit your HTML code appropriately.

<p style="font-size: 0.7em; font-style: italic;">This page has been viewed <?php echo $clicks; ?> times.</p>

And that’s all there is to it.

I distribute code under the GNU GPL. See Copyright & Attribution for details.

12 thoughts on “A Simple Page Click Count System Using PHP And MySQL

  1. Billy

    Love this tutorial, I’m learning PHP and this was really cool, gives me a good feeling because I know a lot of what you’re doing.

  2. manoj

    i am a learning php . and i want to know how to create a new page .

    my problem is that i fetched data from mysql and put the a hyperlink to the first column, and i want when i click on that particular link a new page will open with same name of my first column. evertime
    a new page is created .

  3. John H.

    Wow, I think this is the best explanation of counting click I’ve come across. I’m very VERY new to mysql and PHP, and I can understand all of the variables you have set here. But say I want it to echo how many times a link has been clicked? Should I point the href to a page that says, for instance, “Thanks! Your click has been counted!” or the like, and then echo $clicks on the previous page next to the link which they clicked?

    Essentially all I’m trying to do is to store the number of times a link is clicked, and display that number next to that link. Which is basically what you’re doing here, but it only tracks current pages.

    Help, ideas, thoughts?

    Cheers,

    JH

  4. jhirleman

    Is there a way for me to tailor this to count how many times a link is clicked versus how many times a page is loaded? Or can I point this to a thank you page or something then echo how many times THAT has been loaded next to the link I’m watching? I’m looking to display the number of clicks right next to the link that’s being watched.

  5. Pingback: jhirleman on "Click counters" | Upgrade Wordpress Now

  6. Doug Vanderweide Post author

    Yes, that can be done. Give me a few days and I will blog a solution for your question. Or, if you would like me to act sooner, check out my Amazon.com wish list (linked under “Need More Help Or Want To Say Thanks?”) and make a purchase.

  7. hussain

    Dear,it is good code. but there is error, when I excute the code, it prints every time 0.
    what is reason.

  8. Marco

    I just came across this page by chance, but i noticed 2 basic flaws in this example:

    The first is the most serious: you do not sanitize user supplied values in your query. You are using $curPage without a mysql_real_escape_string() or equivalent, possibly opening your script to SQL-Injections.

    The 2nd is that you are increasing the counter in PHP. There could be a time difference between reading the old value from the database and writing the new because of locking conditions and the delay of writes over reads. On a busy site this could lead to incorrect counts. Using MySQL itself to increase the counter (ie. SET `count` = `count` + 1) would not have this problem. Unless you need the current count in your script there would also be no need anymore to first read the old value and reduce the number of queries executed.

  9. Doug Vanderweide Post author

    @Marco: There is no need to sanitize PHP_SELF in this case. While it is true that someone could append a query string or the like to the URL, and thus inject a script or the like, since the server generates PHP_SELF and would encode any usescapled, dangerous SQL on a round trip, it’s unnecessary. If we were going to render back to the screen, then yes, we would want to sanitize. But if it makes you feel better, sure, go ahead and escape it.

    And yes, on a busy site one would need a solution that accommodates concurrent database locks. People asking programming questions on Yahoo Answers aren’t coding for busy sites, however. Trust me on that.

Leave a Reply