====== Making a professional counter in PHP ======

**How to make your your own counter, and free!!!**

This page explains how to create a professional php counter in your page, or how to use mine to put a counter in your page. Mine is a real free counter that I will keep hosted in this pages as far as I maintain them. 

===== How to put the counter in your page =====

If your website is called "www.example.com", you will have to cut and paste the following code snippet in your page: 

<code html>
<a href="http://www.supermanhamuerto.com/stats.php?site=example.com&page=main" target="_blank">
<img src="http://www.supermanhamuerto.com/cont2.php?site=example.com&page=main">
</a>
</code>


Suppose you want to have many counters in your page; let's say that one is for a public site, other is for the mailing lists, other is for whatever: you can use the 'page' parameter to personalize the counters you want to have. 

If you are only interested in the use of the counter, you are done. If you want to make your own counter, please keep reading. 

===== Things you must have =====

In order that the following code works properly, you have the following in your website: 

  * a running mysql database (I won't consume much resources of it, but I need a space to keep track of the sessions and the counter values)
  * permission to run php in your web site (later we will go over this)
  * the "gd" graphics libary installed in your system

==== Setting up the database ====

It is desirable to have a mysql database with a user with select, update, delete and insert permission over two tables. Here is the code you have to execute as root in order to create things properly: 

<code sql>

create database freecounter; // or the name you want to give to this 

use freecounter;

create table if not exists session
 (site        varchar(250),
  page        tinytext,
  sessionid   varchar(50),
  creation    datetime, 
  last        datetime,
  useragent   tinytext, 
  ip          varchar(50),
  pages       int(16));

create table if not exists counter
(site    varchar(250),
 page    tinytext, 
  count   int(16));
  
grant select, insert, delete, update 
  on freecounter.* to freecounter 
  identified by 'CHOOSE A PASSWORD HERE';

</code>


Check that you have done things well: 

<code sql>

# mysql -u freecounter -p 
password: CHOOSE A PASSWORD HERE

mysql> use freecounter; 

mysql> select * from session; 

mysql> select * from counter; 

</code>

==== Test that php is running in your site ====

Create a file "hello.php" with the following: 

<code php>
<?php

echo( "hello world" ); 

?>
</code>

And check that you see it correctly in a web browser. Let's say that your website is "www.example.com". So, to see this page you should type in a web browser the following: ''http://www.example.com/hello.php''. And you should see the message "hello world". If not, check things.


==== Check that GD library is installed and working ====

Edit again the "hello.php" and change by the following: 

<code php>
<?php
// Create a 100*30 image
$im = imagecreate(100, 30);

// White background and blue text
$bg = imagecolorallocate($im, 255, 255, 255);
$textcolor = imagecolorallocate($im, 0, 0, 255);

// Write the string at the top left
imagestring($im, 5, 0, 0, 'Hello world!', $textcolor);

// Output the image
header('Content-type: image/png');

imagepng($im);
imagedestroy($im);
?>


</code>

And press the "refresh" button of your browser or open a new one. You should see again the message "Hello world", but this time is a picture showing this message instead of a message (if you don't understand what I'm saying, probably you shouldn't have to continue reading some points ago). 

==== And finally the code ====


And finally, the code. You have to create two pages, ''cont2.php'' and ''stats.php''. Yes, I could have an include page to put the functions, but I do not want to have all the things properly one: an opportunity to you to enhace things a bit. However, there are the source code: 

=== cont2.php ===


<code php>
<?php

// create table if not exists session
// (site        varchar(250),
//  page        tinytext,
//  sessionid   varchar(50),
//  creation    datetime, 
//  last        datetime,
//  useragent   tinytext, 
//  ip          varchar(50),
//  pages       int(16));

//create table if not exists counter
//(site    varchar(250),
// page    tinytext, 
//  count   int(16));
  
//  grant select, insert, delete, update 
//  on freecounter.* to freecounter 
//  identified by 'XXXXX';

//
// pick up request parameters
//
import_request_variables( 'G', 'p_' ); 

//$p_site = 'www.example.com'; 
//$page = ''; 


// session time in minutes
$session_time = 30;

//
// session data
//
session_set_cookie_params( $session_time * 60 ); // sessions will last only 30 minutes

session_start();

$ip = $_SERVER['REMOTE_ADDR'];
$useragent = $_SERVER['HTTP_USER_AGENT'];

$db_host='localhost';
$db_username ='freecounter';
$db_password ='PUT THE PASSWORD HERE'; 
$database ='freecounter';

$conn = mysql_connect( $db_host, $db_username, $db_password ); 

mysql_select_db($database);


set_session_data( $conn, 
                  $p_site,
                  $p_page, 
                  $ip,
                  session_id(),
                  $useragent,
                  $session_time );

$counter = get_counter( $conn, $p_site, $p_page );

output_image( $counter, $ip ); 

clear_old_data( $conn );

mysql_close($conn);



// output a graphic image with the counter value
// and other data 
function output_image( $counter, $ip )
{

  $im = imagecreate( 100, 60 ); 
  
  $bg = imagecolorallocatealpha( $im, 0, 0, 0, 127 ); // transparent
  $black = imagecolorallocatealpha( $im, 0, 0, 0, 0 ); 
  
  imagestring( $im, 5, 20, 2, $counter, $black ); 
  
  imagestring( $im, 2, 11, 20, 'Your ip is: ', $black ); 
  
  imagestring( $im, 2, 3, 32, $ip, $black );
  
  header('Content-type: image/jpg');
  
  imagepng($im);
  
  imagedestroy( $im ); 
  
}

// get the counter current value
function get_counter( $conn, $site, $page ) 
{
  $query = sprintf( "select count from counter "
                    ." where site = '%s' "
                    ." and page = '%s' ", 
                    mysql_real_escape_string( $site ), 
                    mysql_real_escape_string( $page ) ); 
                    
    $result = mysql_query( $query ); 
    
    $row = mysql_fetch_assoc( $result ); 
    
    if( $row ) 
      return $row['count'];
    else
      return 0; 
          
} // get_counter

// updates the counter data 
function set_counter_data( $conn, $site, $page )
{
    $query = sprintf( "select * from counter "
                     ." where site = '%s' "
                     ."  and page = '%s' ", 
                     mysql_real_escape_string($site),
                     mysql_real_escape_string($page) ); 
                     
    $result = mysql_query( $query ); 
    
    $found = false; 
    $row = mysql_fetch_assoc( $result ); 
    if( $row ) 
    {
      $found = true; 
    }
    
    if( $found ) 
    {
      $update = sprintf( "update counter set count = count + 1 "
                          ." where site = '%s' "
                          ." and page = '%s' ", 
                          mysql_real_escape_string($site), 
                          mysql_real_escape_string($page) );
      
      $result = mysql_query( $update ); 
    }
    else
    {
      $insert = sprintf( "insert into counter (site, page, count) "
                        ."values ( '%s', '%s', 1 )",
                        mysql_real_escape_string($site), 
                        mysql_real_escape_string($page) ); 
      $result = mysql_query( $insert ); 
      
    } // $found 

} // set_counter_data

// all session data older than 1 year is deleted 
// from the session table
function clear_old_data( $conn )
{
  $now = strftime( '%G-%m-%d %H:%M:%S %z', mktime() );
  $twoYearAgo = strftime( '%G-%m-%d %H:%M:%S %z', mktime() - (2 * 365 * 24 * 60 * 60) );
  
  $delete = sprintf( "delete from session where creation <= '%s'", 
                     mysql_real_escape_string( $twoYearAgo ) ); 
  
  $result = mysql_query( $delete ); 
  
} // clear old data

// function to update statistical data
// per session: pages visited, user agent
// clicks, time spent
function set_session_data( $conn, $site, $page, $ip, $session, $useragent, $session_time )
{

    $now = strftime( '%G-%m-%d %H:%M:%S %z', mktime() );

    $query = sprintf( "select * from session "
                      ." where sessionid = '%s' "
                      ." and site = '%s' "
                      ." and page = '%s'",
                        mysql_real_escape_string($session),
                        mysql_real_escape_string($site),
                        mysql_real_escape_string($page) );

    $result = mysql_query( $query );

    
    $found = false;
    $row = mysql_fetch_assoc( $result ); 
    if( $row ) 
    {
      $found = true; 
    }    
    else
    {
      // we will try to find the same ip 
      // in the last half hour
      $a_half_ago = strftime( '%G-%m-%d %H:%M:%S %z', mktime() - ($session_time * 60) );

      $query = sprintf( "select * from session "
                        ." where ip = '%s' "
                        ." and creation >= '%s' "
                        ." and site = '%s' "
                        ." and page = '%s'",
                          mysql_real_escape_string($ip),
                          mysql_real_escape_string($a_half_ago),
                          mysql_real_escape_string($site),
                          mysql_real_escape_string($page) );

      $result = mysql_query( $query );

      $row = mysql_fetch_assoc( $result ); 
      if( $row ) 
      {
        $found = true; 
      } // $row    
       
    } // $row

    if( $found )
    {
      $update = sprintf( "update session set pages = pages + 1, "
                        ." useragent = '%s', "
                        ." ip = '%s', "
                        ." last = '%s' "
                        ."where site = '%s' and page = '%s' and sessionid = '%s'" ,
                                   mysql_real_escape_string( $useragent ), 
                                   mysql_real_escape_string( $ip ), 
                                   mysql_real_escape_string( $now ), //last
                                   mysql_real_escape_string( $site ),  // site
                                   mysql_real_escape_string( $page ), 
                                   mysql_real_escape_string( $session ) ); //

      $result = mysql_query( $update ); 
      
    } 
    else
    {
      $insert = sprintf( "insert into session (site, page, sessionid, creation, last, useragent, ip, pages) "
                                         ." values( '%s',  '%s',  '%s', '%s', '%s', '%s', '%s', %s )", 
                                   mysql_real_escape_string( $site ),  // site
                                   mysql_real_escape_string( $page ), 
                                   mysql_real_escape_string( $session ), // sessionid
                                   mysql_real_escape_string( $now ), // creation
                                   mysql_real_escape_string( $now ), // last 
                                   mysql_real_escape_string( $useragent ), // useragent
                                   mysql_real_escape_string( $ip ), // ip
                                   mysql_real_escape_string( 1 ) );

      $result = mysql_query( $insert ); 

      set_counter_data( $conn, $site, $page );                  
      
    } 

} // set_session_data



?>
</code>


=== stats.php ===

<code.php>
<?php

$month = array( "name" => array( 'Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre', 'End of Year' ),
                "value" => array( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ), 
                "date" => array( strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 1, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 2, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 3, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 4, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 5, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 6, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 7, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 8, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 9, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 10, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 11, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 12, 1, date("Y",mktime())+1) ),
                                 strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 12, 31, date("Y",mktime())+1) )
                               ) ); 


//
// pick up request parameters
//
import_request_variables( 'G', 'p_' ); 

//$p_site = 'www.example.com'; 
//$p_page = ''; 

$db_host='localhost';
$db_username ='freecounter';
$db_password ='PUT HERE THE PASSWORD'; 
$database ='freecounter';

$conn = mysql_connect( $db_host, $db_username, $db_password ); 

mysql_select_db($database);


// INFORMATION TO DISPLAY: 
//
// Current value of the counter: XXXXXXX
//                              Visits     Average pages   Average time
//                                          visited          spent
// Last hour:                    XXX         YYY             ZZZ
// During today:                 XXX         YYY             ZZZ
// Visits during current month:  XXX         YYY             ZZZ
// Visits during month-1:        XXX         YYY             ZZZ
// During the year:              XXX         YYY             ZZZ
// 


$now = strftime( '%G-%m-%d %H:%M:%S %z', mktime() );
$an_hour_ago = strftime( '%G-%m-%d %H:%M:%S %z', mktime() - (60*60) );
$a_day_ago = strftime( '%G-%m-%d %H:%M:%S %z', mktime() - (24*60*60) );


$begin_of_year = strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 1, 1, date("Y",mktime())+1) );
$end_of_year = strftime( '%G-%m-%d %H:%M:%S %z', mktime( 0, 0, 0, 12, 31, date("Y",mktime())+1) );

$row_year = get_site_stats( $conn, $p_site, $p_page, $begin_of_year, $now );
$row_hour =get_site_stats( $conn, $p_site, $p_page, $an_hour_ago, $now ); 
$row_day = get_site_stats( $conn, $p_site, $p_page, $a_day_ago, $now );


for( $i = 0; $i < 12; $i++ )
{
  //echo( "<h1>".$month["name"][$i]." ".$month["value"][$i].$month["date"][$i]."</h1>" );

  $month["stats"][$i] = get_site_stats( $conn, $p_site, $p_page, $month["date"][$i], $month["date"][$i+1] );
} // for



?>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>

<body>

<br/>
<br/>
<h1><?php echo(htmlspecialchars($p_site));?></h1>
<br/>
<strong>Valor actual del contador: <?php echo( get_counter( $conn, $p_site, $p_page ) );?></strong>
<br/>
<br/>
<br/>
<table>
  <tr>
    <td></td>
    <td>Visitas</td>
    <td title="Cuantas paginas consulta cada visita">Paginas visitadas</td>
    <td title="Tiempo medio que cada visita, en minutos">Tiempo por visita</td>        
  </tr>
<?php
  echo("<tr>\n\r");
  echo("<td>La última hora</td>\n\r");
  echo("<td>".$row_hour['visits']."</td>\n\r");
  echo("<td>".round($row_hour['avg_pages'],1)."</td>\n\r");
  echo("<td>".round($row_hour['avg_time_spent']/60,1)."</td>\n\r");
  echo("</tr>\n\r");

  echo("<tr>\n\r");
  echo("<td>La últimas 24 horas</td>\n\r");
  echo("<td>".$row_day['visits']."</td>\n\r");
  echo("<td>".round($row_day['avg_pages'],1)."</td>\n\r");
  echo("<td>".round($row_day['avg_time_spent']/60,1)."</td>\n\r");
  echo("</tr>\n\r");

for( $i = 0; $i < 12; $i++ )
{
  echo("<tr>\n\r");
  echo("<td>".$month['name'][$i]."</td>\n\r");
  echo("<td>".$month['stats'][$i]['visits']."</td>\n\r");
  echo("<td>".round($month['stats'][$i]['avg_pages'],1)."</td>\n\r");
  echo("<td>".round($month['stats'][$i]['avg_time_spent']/60,1)."</td>\n\r");
  echo("</tr>\n\r");
} // for

  echo("<tr>\n\r");
  echo("<td>En todo el año</td>\n\r");
  echo("<td>".$row_year['visits']."</td>\n\r");
  echo("<td>".round($row_year['avg_pages'],1)."</td>\n\r");
  echo("<td>".round($row_year['avg_time_spent']/60,1)."</td>\n\r");
  echo("</tr>\n\r");




?>
</table>
<br/>
<br/>
<br/>
<center><small>Contador proporcionado por <a href="http://www.supermanhamuerto.com/doku.php?id=linux:abettercounter">www.supermanhamuerto.com</a></small></center>
</body>
</html>
<?php

mysql_close($conn);


// 
// get site statistics in order to display
// in a fancy table 
// 
function get_site_stats( $conn, $site, $page, $from, $to ) 
{
  $query = sprintf( "select count(*) as visits, "
                   ."    avg(pages) as avg_pages, "
                   ."    avg(last-creation) as avg_time_spent "
                   ."from session "
                   ."where site = '%s' "
                   ." and page = '%s' "
                   ." and '%s' <= creation "
                   ." and creation < '%s' ",
                   mysql_real_escape_string( $site ), 
                   mysql_real_escape_string( $page ),
                   mysql_real_escape_string( $from ), 
                   mysql_real_escape_string( $to ) ); 
                   
  $result = mysql_query( $query ); 
  
  $row = mysql_fetch_assoc( $result ); 
  
  return $row;

} // get_site_stats

// get the counter current value
function get_counter( $conn, $site, $page ) 
{
  $query = sprintf( "select count from counter "
                      ." where site = '%s' "
                      ." and page = '%s' ", 
                       mysql_real_escape_string( $site ), 
                       mysql_real_escape_string( $page ) ); 
                                            
  $result = mysql_query( $query ); 
                          
  $row = mysql_fetch_assoc( $result ); 
      
  if( $row ) 
    return $row['count'];
  else
   return 0; 
            
} // get_counter
                                                                                                                                                    
?>
</code>


==== Finally, wrap it alltogether ====


You can make a simple html page to see how it looks like: 

<code html>
<html>

<body bgcolor="silver">

<a href="http://www.supermanhamuerto.com/stats.php?site=example.com&page=main" target="_blank">
<img src="http://www.supermanhamuerto.com/cont2.php?site=example.com&page=main">
</a>

</body>

</html>
</code>

And that's all. Enjoy, and if you think that this is useful, please comment out here or recommend to your friends and colleagues. 

~~DISCUSSION~~