Click here to Skip to main content
Click here to Skip to main content

Site Statistics with PHP and MySQL

, 22 Apr 2009
Rate this:
Please Sign up or sign in to vote.
Site statistics with PHP and MySQL

Introduction

Keep a record of number of visits and the unique visitors, and show the result as numbers and graphs, as shown below:

graph.png

Background

I have written all the code in PHP to achieve this functionality and used MySql to manage its data. For graphs I used PHPGraphLib modules.

Initially I used a PHP graphical hit counter to count the visits on my site, that stores the data in a file on the server. But I gradually changed it completely to store data in a MySql database, by keeping its graphical digits display techniques as it is. I also extended the same module to count both visits and unique visitors. Before using this code, you need to understand the usage of PHP graphical hit counter and PHPGraphLib.

Code Files

There are three main files in the project count.php, graph.php and index.php.

count.php

This file contains all of the coding to access database and to display the graphic digits:

<?php

include("configuration.php");
/* Get page and log file names */
$page       = input($_GET['page']) or die('ERROR: Missing page ID');    
$timestampInSeconds = $_SERVER['REQUEST_TIME']; 
$mySqlDateTime= date("Y-m-d H:i:s", $timestampInSeconds);
$sql = 'INSERT INTO '.$tableName.'(`id`, `Section`, `Date`, `IP`) 
VALUES (NULL, \''.$page.'\',\''.$mySqlDateTime.'\', \''.$_SERVER['REMOTE_ADDR'].'\');';
mysql_select_db($database, $con);
mysql_query($sql);

$query='SELECT COUNT( * ) total FROM  '.$tableName.' where section=\''.$page.'\'';
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
$count = $row[0];

$query='SELECT  count(distinct IP) FROM '.$tableName.' where section=\''.$page.'\'';
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
$UniaquCount = $row[0];
mysql_close($con);

/* Get style and extension information */
$style      = input($_GET['style']) or $style = $default_style;
$style_dir  = 'styles/' . $style . '/';
$ext        = input($_GET['ext']) or $ext = $default_ext;

    $count = $count + 1;      
    if ($min_digits > 0) 
        $count = sprintf('%0'.$min_digits.'s',$count);
    
    /* Print out Javascript code and exit */
    echo 'document.write(\'&nbsp;&nbsp;&nbsp;Vists:&nbsp;\');';
    $len = strlen($count);

    for ($i=0;$i<$len;$i++)
        echo 'document.write(\'<img src="'.$base_url . $style_dir . substr(
           $count,$i,1) . '.' . $ext .'" border="0">\');';    

    echo 'document.write(\'<br>\');';
    echo 'document.write(\'Vistors:&nbsp;\');';
    $len = strlen($UniaquCount);    
    for ($i=0;$i<$len;$i++) 
        echo 'document.write(\'<img src="'.$base_url . $style_dir . substr(
            $UniaquCount,$i,1) . '.' . $ext .'" border="0">\');';    
   exit();
?>

graph.php

This file contains the code to generate the graphical representation of data:

<?php 
include("configuration.php");
$page = input($_GET['page']) or die('ERROR: Missing page ID');
$query='SELECT date, count(*),
    count(distinct IP) FROM `'.$tableName.'`  
	where section=\''.$page.'\' group by date order by date';
$result=mysql_query($query) or die('Query failed: ' . mysql_error());
$fields=mysql_num_fields($result);
$num=mysql_numrows($result);
$loopCounter = 0;
$data = array();
$data2 = array();
while($ris=mysql_fetch_row($result))
    {              
       $data[$ris[0]]=$ris[1];     
       $data2[$ris[0]]=$ris[2];       
    } 

mysql_close($con);
include("phpgraphlib.php"); 
$graph=new PHPGraphLib(600,250);
$graph->addData($data,$data2);
$graph->setTitle("Site Statistics");
$graph->setBars(false);
$graph->setLine(true);
$graph->setDataPoints(true);
$graph->setDataPointColor("maroon");
$graph->setDataValues(true);
$graph->setDataValueColor("maroon");
$graph->setGoalLine(.0025);
$graph->setGoalLineColor("red");
$graph->setXValuesHorizontal(true);
$graph->createGraph();
?>

index.php

This file has the code that makes a call to count.php and graph.php and displays the result in the browser:

<?php include("configuration.php"); ?>

<script language="Javascript"
    src="<?php echo $base_url;?>count.php?page=<?php echo $_REQUEST['section'];?>">
</script>
<?php
echo "<br>";                

echo '<img src="'.$base_url.'graph.php?page='.$_REQUEST['section'].'" />';      

?>

Using the Code

Download the source zip file and unzip it in a folder on your server.

Creating Database Table

You need to create a simple table in your MySQL database by executing the following script:

CREATE TABLE countdetail (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Section varchar(500) NOT NULL,
  `Date` date NOT NULL,
  IP varchar(50) DEFAULT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Configure the Project

In the first step, you need to configure the project. Set its base URL and database parameters in configuration.php file:

<?php

    // SETUP YOUR COUNTER
        // URL of the folder where script is installed. INCLUDE a trailing "/" !!!
        $base_url = 'http://localhost/MySites/Counter/';        
                
        // database parameters
        $username="root";
        $password="";    
        $servername="localhost";
        $database="mysite";
        $tableName="countdetail";    
    
    // Optional parameters, if not sure leave with default values
        // Default image style (font)
        $default_style = 'web1';
        
        // Default counter image extension
        $default_ext = 'gif';
        
        // Minimum number of digits shown (zero-padding). Set to 0 to disable.
        $min_digits = 0;        
        
    // Don't change anything below
    
    /* Turn error notices off */
        error_reporting(E_ALL ^ E_NOTICE);
        
    $con = mysql_connect($servername,$username,$password);
    if (!$con)
         die('Cannot dadd comments at the moment');
      else
         @mysql_select_db($database) or die( "Unable to select database");
         
    /* This function handles input parameters making sure 
	nothing dangerous is passed in */    
    function input($in) {
    $out = htmlentities(stripslashes($in));
    $out = str_replace(array('/','\\'), '', $out);
    return $out;
    }     
?>

See the Result

Now it is time to see the result, open your browser and type the address of index.php file on the server with the name of the page you want to count the visits for. For example:

Where "yourSiteName" is the name of your page. If all is ok, this should show the result as shown at the top of this article. 

Points of Interest  

You can build your own counter with a very good interface with little effort, and can call it in any another site to keep record of that site. See this code in action on one of my blogs ...  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Ghaffar Khan
Software Developer (Senior)
Pakistan Pakistan
I have been in the field of computer and IT for last eight years, after doing Masters in Computer Science from Karachi University.
 
My programing experience includes C/C++, C#, ASP.NET, PHP, Assembly Language, VC++, OpenGL, VB, FoxPro.
 
My Home City is Karachi. I love to see new places and never miss a chance to go out of the city whenever I get enough time.

Comments and Discussions

 
Questiondifferent color Pinmemberjust1st_8531-Mar-14 23:01 
QuestionShow visitors for the last 30 days only? PinmemberMember 1057827810-Feb-14 5:41 
QuestionError PinmemberChintanupadhayay10-Jan-13 20:27 
Questionhi PinmemberMember 874233528-Mar-12 1:15 
Newsnice Pinmembervir0e525-Mar-11 20:25 
GeneralOptimization PinmemberTuiTo9-Sep-10 9:49 
Generalgreat inspiration Pinmemberfirsan16-Feb-10 11:48 
GeneralGreat Code PinmemberBlagoj Delipetrov26-Nov-09 12:13 
GeneralExcellent Work Pinmemberanthonygallina@attechlive.co.cc29-Apr-09 16:32 
GeneralRe: Excellent Work PinmemberGhaffar Khan29-Apr-09 21:26 
GeneralRe: Excellent Work Pinmemberhtml2class5-May-09 15:58 
GeneralRe: Excellent Work PinmemberGhaffar Khan5-May-09 21:12 
GeneralRe: Excellent Work [modified] Pinmemberhtml2class6-May-09 0:15 
GeneralRe: Excellent Work PinmemberGhaffar Khan6-May-09 3:56 
GeneralRe: Excellent Work Pinmemberhtml2class6-May-09 11:20 
GeneralExcellent workdone !! PinmemberMUZAMIL KHAN25-Apr-09 5:42 
GeneralRe: Excellent workdone !! PinmemberGhaffar Khan25-Apr-09 10:28 
AnswerGood Job Pinmemberkanu@adatapost22-Apr-09 16:28 
AnswerRe: Good Job PinmemberGhaffar Khan23-Apr-09 23:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Apr 2009
Article Copyright 2009 by Ghaffar Khan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid