supermanhamuerto.com

User Tools

Site Tools

Trace:
linux:abettercounter

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
linux:abettercounter [2011/04/12 12:22] – rlunarolinux:abettercounter [2022/12/02 21:02] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== 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~~
 +
 +