Click here to Skip to main content
15,867,568 members
Articles / Database Development / MySQL

Site Statistics with PHP and MySQL

Rate me:
Please Sign up or sign in to vote.
4.67/5 (12 votes)
22 Apr 2009CPOL2 min read 123.7K   4.7K   43   20
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:

Image 1

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:

SQL
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)


Written By
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

 
SuggestionNew PHP Version Update Pin
Gabee823-Mar-17 23:16
Gabee823-Mar-17 23:16 
Questiondifferent color Pin
just1st_8531-Mar-14 23:01
just1st_8531-Mar-14 23:01 
QuestionShow visitors for the last 30 days only? Pin
Member 1057827810-Feb-14 5:41
Member 1057827810-Feb-14 5:41 
QuestionError Pin
Chintanupadhayay10-Jan-13 20:27
Chintanupadhayay10-Jan-13 20:27 
Questionhi Pin
Member 874233528-Mar-12 1:15
Member 874233528-Mar-12 1:15 
Newsnice Pin
vir0e525-Mar-11 20:25
vir0e525-Mar-11 20:25 
GeneralOptimization Pin
Gldmelo9-Sep-10 9:49
Gldmelo9-Sep-10 9:49 
Generalgreat inspiration Pin
firsan16-Feb-10 11:48
firsan16-Feb-10 11:48 
GeneralGreat Code Pin
Blagoj Delipetrov26-Nov-09 12:13
Blagoj Delipetrov26-Nov-09 12:13 
GeneralExcellent Work Pin
anthonygallina@attechlive.co.cc29-Apr-09 16:32
anthonygallina@attechlive.co.cc29-Apr-09 16:32 
GeneralRe: Excellent Work Pin
Ghaffar Khan29-Apr-09 21:26
Ghaffar Khan29-Apr-09 21:26 
GeneralRe: Excellent Work Pin
html2class5-May-09 15:58
professionalhtml2class5-May-09 15:58 
GeneralRe: Excellent Work Pin
Ghaffar Khan5-May-09 21:12
Ghaffar Khan5-May-09 21:12 
GeneralRe: Excellent Work [modified] Pin
html2class6-May-09 0:15
professionalhtml2class6-May-09 0:15 
GeneralRe: Excellent Work Pin
Ghaffar Khan6-May-09 3:56
Ghaffar Khan6-May-09 3:56 
GeneralRe: Excellent Work Pin
html2class6-May-09 11:20
professionalhtml2class6-May-09 11:20 
Hi If I put this directly into my browser
localhost/TonysSite/GLASTOPFsite/test/Countertest/index.php?section=test

I get the numbers displayed. But no graph.

? Could it be because I have a folder named test in my path? Sniff | :^)
GeneralExcellent workdone !! Pin
MUZAMIL KHAN25-Apr-09 5:42
MUZAMIL KHAN25-Apr-09 5:42 
GeneralRe: Excellent workdone !! Pin
Ghaffar Khan25-Apr-09 10:28
Ghaffar Khan25-Apr-09 10:28 
AnswerGood Job Pin
adatapost22-Apr-09 16:28
adatapost22-Apr-09 16:28 
AnswerRe: Good Job Pin
Ghaffar Khan23-Apr-09 23:52
Ghaffar Khan23-Apr-09 23:52 

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

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