Creating a Hitcounter with MySQL and PHP

While there are a lot of free ways to get traffic reports on your site, I came up with this idea for a hitcounter for a site that I had written by hand just so I could pull my own data off it.  It’s nice to have something you can just drop into a site, but doesn’t necessarily give you what you want.  Also doing it yourself gives you control over how long you keep the data.  I use this hitcounter with multiple PHP applications (Joomla, PHPBB3, and some more), but also with websites that I’ve handcrafted.  I haven’t put it into wordpress yet, but here we go.

MySQL Setup

The first thing we need to know is what data do we want to collect?  Here’s what I came up with:

  • IP Address of the client (of course)
  • Browser
  • Page (The page the client is hitting e.g. “/index.php” or “/index.php?action=news”)
  • Host (The host the client is viewing e.g. “www.nernonline.net” or “forum.nernonline.net”)
  • Time

All of this is pretty simple to grab from a PHP script, but we’ll go into that a bit later.  For now, we need to setup the MySQL table.  I use a separate database for my hitcounter table, not linked with any of the other databases that are running on the same box.  Copy this code into “hitcounter.sql” and run it by typing “mysql < hitcounter.sql”.  This is of course assuming that you are on your box, have the mysql command line interface installed, and have write permissions.

  1. – Create the our blank database
  2. CREATE DATABSE `hitcounter`;
  3.  
  4. USE hitcounter;
  5. – Hitcounter table
  6. CREATE TABLE `hits` (
  7.         `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  8.         `ip` TEXT NOT NULL,
  9.         `browser` TEXT NOT NULL,
  10.         `page` TEXT NOT NULL,
  11.         `host` TEXT NOT NULL,
  12.         `TIME` DATETIME NOT NULL);

The next step is to create a user that can write to this database.  All of your databases should have different users, with different passwords…Just for a bit of security : ).  You can run this either at the MySQL prompt or by putting it in a file and doing the same technique as above.

  1. GRANT ALL ON `hitcounter`.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Obviously change “username” and “password” to something thats a little more meaningful and secure : ).  Also, the @’localhost’ portion tells mysql that this user can NOT login unless the connection is coming from the box itself.  If your Apache / IIS is on the same box as your database, leave localhost there.  If you need this user to be able to login from anywhere, change that to @’%', though I don’t know why you would for a hitcounter ; ).

PHP Stuffs

Now that our database is setup, we need to get something to go into it.  I use pear’s MDB2 for this.  If you don’t have it installed, on a Linux system you can type “pear install MDB2″ to get it, but I don’t know what to do if your running a Windows server, sorry.  I have a seperate .php file that I include with my globals.php file, so it is loaded every time a page is requested.

I have two functions inside my php file, one for updating the hits to a website, and one for getting the number of hits for a particular website.  Here’s the update function:

  1. function updatehits() {
  2.         // Include Pear's MDB2
  3.         require_once("MDB2.php");
  4.  
  5.         // Build our connection "string" (array)
  6.         $dsn = array(
  7.                 'phptype' => 'mysqli',
  8.                 'username' => 'username',
  9.                 'password' => 'password',
  10.                 'hostspec' => 'localhost',
  11.                 'database' => 'hitcounter' );
  12.  
  13.         // Create a singleton DB Connection
  14.         $hitdb = MDB2::singleton($dsn);
  15.  
  16.         // Get all the information we care about
  17.         $ip = $_SERVER['REMOTE_ADDR'];
  18.         $host = strtolower($_SERVER['HTTP_HOST']);
  19.         $browser = $_SERVER['HTTP_USER_AGENT'];
  20.         $page = $_SERVER['REQUEST_URI'];
  21.  
  22.         if($host == "" || $ip == "::1") {
  23.                 return;
  24.         }
  25.  
  26.         $sql = "INSERT INTO hits (ip, browser, page, host, time) VALUES ('$ip', '$browser', '$page', '$host', NOW());";
  27.  
  28.         $hitdb->query($sql);
  29.  
  30.         if(PEAR::iserror($hitdb)) {
  31.                 die($hitdb->getMessage());
  32.         }
  33. }

Of course, your going to want to replace username and password again.  This function pulls all the information that we care about out of PHP and dumps it nicely into our database.  The next function gets the amount of hits for the current website that your at.  I use this to put the “This site has X hits since Whenever” stuff at the bottom of the page or wherever.

  1. function gethits() {
  2.  
  3.         require_once("MDB2.php");
  4.  
  5.         // Build our connection "string" (array)
  6.         $dsn = array(
  7.                 'phptype' => 'mysqli',
  8.                 'username' => 'username',
  9.                 'password' => 'password',
  10.                 'hostspec' => 'localhost',
  11.                 'database' => 'hitcounter' );
  12.  
  13.         $hitdb = MDB2::singleton($dsn);
  14.  
  15.         // SQL query to get the hits from the website currently being viewed
  16.         $sql = "SELECT COUNT(*) FROM hits WHERE host = '" . strtolower($_SERVER['HTTP_HOST']) . "';";
  17.  
  18.         $result = $hitdb->query($sql);
  19.  
  20.         if($result->numRows() < 1) {                 return ('0');         }         $row = $result->fetchRow();
  21.  
  22.         return ($row[0]);
  23. }

Now then, after you have those two functions in your file, all you have to do is include that file with something that gets included no matter what page is hit on your website.  Usually index.php or something similar.

  1. require_once('updatehits.php');
  2. updatehits();

Anywhere you want to display how many hits that webpage has, just call the “gethits();” function, and it returns an unformatted number, which you can format any way you like using php’s number_format function.

Helpful MySQL Scripts

I use these MySQL snippets all the time, to check how many hits my webpages have gotten, unique IPs, etc.

Check how many hits your websites get by host:

  1. SELECT `host`, COUNT(*)
  2. FROM `hits`
  3. GROUP BY `host`;

Total hits by day:

  1. SELECT DATE(TIME) "Day", count(*) "Hits"
  2. FROM `hits`
  3. GROUP BY DATE(TIME);

Total hits by week:

  1. SELECT WEEK(TIME) "Week", count(*) "Hits"
  2. FROM `hits`
  3. GROUP BY WEEK(TIME);

To get unique IPs per month:

  1. SELECT COUNT(ip), MONTH(TIME)
  2. FROM (
  3. SELECT ip, TIME
  4. FROM hits
  5. GROUP BY ip ) as foo
  6. GROUP BY MONTH(TIME);

And that should do it!

2009
09/28
POSTED BY
LoONeYChIKuN
CATEGORY
LAMP
TAGS

Write comment
HITS ON THIS PAGE
2,434
    • Oculo Macner
    • October 2nd, 2009

    This looks awesome, can you package it as a WP Plugin?

  2. Nice script. More creative than a few I have looked at. But all the hit counter scripts I have seen, including yours, fall into one of two categories (a) displaying a hit counter on a page or (b) tracking hits in a MySQL database without displaying them on the page.

    But I manage an old site with close to 800 pages using old FrontPage hit counters. I need to convert those and would like to use a MySQL approach, without giving up the visible counters that exist.

    I’m looking for a PHP/MySQL script that will manage any number of VISIBLE page counters. I’m surpised that no one sems to offer this.

  3. Sorry guys, I threw this blog together right before I went on vacation and haven’t looked at it since I left. TBH I didn’t think anyone would even make it here before I had more posts up.

    @Oculo Macner
    Instead of working on another blog today I decided to create a WP plugin for just that : ). WP hasn’t approved the plugin yet (I just submitted it), but it should be in the directory pretty soon.

    You can download it from here too under WP Plugins page.

    @Michael Gora
    I’m not familiar with FrontPage counters, and I’m not 100% sure I know what you talking about with Visible hit counters. However, I am willing to work with you to help you get whatever it is you need sorted out.

  1. No trackbacks yet.