How to Count Visitors Using PHP & MySQL

This example tutorial will show how to count visitors by using PHP and MySLQ, you can count all visitor visited your website by ip, hour, minute, date, month, year, page, browser, referrer and it is stored in MySQL. So you can get detail information about your visitors.

Tracking our website’s visitors is a very important step if you’re serious enough about analyzing your traffic and optimizing your pages to get the most of your visitors. There are many reasons why you should think of implementic tracking scripts (it’s crucial to know where your traffic is coming from and where it goes, wha people search, how long they stay etc.), you could increase sales, you could optimize your pages to increase page hits, you could make lots of changes to increase your Adsense profits and the list goes on and on.

Bellow is tutorial how to count visitors using php and MySQL, follow with this step :

First step you need create visitors_table. Copy code bellow and pasted it Query SQL, you will get a table “visitors_table’.

CREATE TABLE `visitors_table` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`visitor_ip` VARCHAR( 32 ) NULL ,
`visitor_browser` VARCHAR( 255 ) NULL ,
`visitor_hour` SMALLINT( 2 ) NOT NULL DEFAULT '00',
`visitor_minute` SMALLINT( 2 ) NOT NULL DEFAULT '00',
`visitor_date` TIMESTAMP( 32 ) NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`visitor_day` SMALLINT( 2 ) NOT NULL ,
`visitor_month` SMALLINT( 2 ) NOT NULL ,
`visitor_year` SMALLINT( 4 ) NOT NULL ,
`visitor_refferer` VARCHAR( 255 ) NULL ,
`visitor_page` VARCHAR( 255 ) NULL
) TYPE = MYISAM ;

Ok. We have our database ready for storing our visitors info We will need to setup a script which will store the visitor’s info in our database so let’s start writing it. We need the ip address of our visitor so we will get it using the following method.

function getBrowserType () {
if (!empty($_SERVER['HTTP_USER_AGENT']))
{
   $HTTP_USER_AGENT = $_SERVER['HTTP_USER_AGENT'];
}
else if (!empty($HTTP_SERVER_VARS['HTTP_USER_AGENT']))
{
   $HTTP_USER_AGENT = $HTTP_SERVER_VARS['HTTP_USER_AGENT'];
}
else if (!isset($HTTP_USER_AGENT))
{
   $HTTP_USER_AGENT = '';
}
if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[2];
   $browser_agent = 'opera';
}
else if (ereg('MSIE ([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'ie';
}
else if (ereg('OmniWeb/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'omniweb';
}
else if (ereg('Netscape([0-9]{1})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'netscape';
}
else if (ereg('Mozilla/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'mozilla';
}
else if (ereg('Konqueror/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'konqueror';
}
else
{
   $browser_version = 0;
   $browser_agent = 'other';
}
return $browser_agent;
}

Here is browser types code:
$visitor_browser = getBrowserType();
Now we need to define hour, minute, day, month and year of visitors:

$visitor_hour = date("h");
$visitor_minute = date("i");
$visitor_day = date("d");
$visitor_month = date("m");
$visitor_year = date("y");

And next we need to find out who is sending us visitors so we can thank them.

$visitor_refferer = gethostbyname($HTTP_REFERER);

So to get the full url of our page we will use this function:

function selfURL() {
$s = empty($_SERVER["HTTPS"]) ? '' : ($_SERVER["HTTPS"] == "on") ? "s" : "";
$protocol = strleft(strtolower($_SERVER["SERVER_PROTOCOL"]), "/").$s;
$port = ($_SERVER["SERVER_PORT"] == "80") ? "" : (":".$_SERVER["SERVER_PORT"]);
return $protocol."://".$_SERVER['SERVER_NAME'].$port.$_SERVER['REQUEST_URI'];
}
function strleft($s1, $s2) { return substr($s1, 0, strpos($s1, $s2));
}

Now we have our page, we will store it on a variable:

$visited_page = selfURL();

We need to create a new page which will be used to connect to the database.

It is visitors_connections.php. Copy this code and save it:

$hostname_visitors = "host";
$database_visitors = "database";
$username_visitors = "username";
$password_visitors = "password";

$visitors = mysql_connect($hostname_visitors, $username_visitors,
 $password_visitors) or rigger_error(mysql_error(),E_USER_ERROR);

function getBrowserType () {
if (!empty($_SERVER['HTTP_USER_AGENT']))
{
   $HTTP_USER_AGENT = $_SERVER['HTTP_USER_AGENT'];
}
else if (!empty($HTTP_SERVER_VARS['HTTP_USER_AGENT']))
{
   $HTTP_USER_AGENT = $HTTP_SERVER_VARS['HTTP_USER_AGENT'];
}
else if (!isset($HTTP_USER_AGENT))
{
   $HTTP_USER_AGENT = '';
}
if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[2];
   $browser_agent = 'opera';
}
else if (ereg('MSIE ([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'ie';
}
else if (ereg('OmniWeb/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'omniweb';
}
else if (ereg('Netscape([0-9]{1})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'netscape';
}
else if (ereg('Mozilla/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'mozilla';
}
else if (ereg('Konqueror/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version))
{
   $browser_version = $log_version[1];
   $browser_agent = 'konqueror';
}
else
{
   $browser_version = 0;
   $browser_agent = 'other';
}
return $browser_agent;
}

function selfURL() {
$s = empty($_SERVER["HTTPS"]) ? '' : ($_SERVER["HTTPS"] == "on") ? "s" : "";
$protocol = strleft(strtolower($_SERVER["SERVER_PROTOCOL"]), "/").$s;
$port = ($_SERVER["SERVER_PORT"] == "80") ? "" : (":".$_SERVER["SERVER_PORT"]);
return $protocol."://".$_SERVER['SERVER_NAME'].$port.$_SERVER['REQUEST_URI'];
}

function strleft($s1, $s2) { return substr($s1, 0, strpos($s1, $s2)); }

function paginate($start,$limit,$total,$filePath,$otherParams) {
    global $lang;

    $allPages = ceil($total/$limit);

    $currentPage = floor($start/$limit) + 1;

    $pagination = "";
    if ($allPages>10) {
        $maxPages = ($allPages>9) ? 9 : $allPages;

        if ($allPages>9) {
            if ($currentPage>=1&&$currentPage<=$allPages) {
                $pagination .= ($currentPage>4) ? " ... " : " ";

                $minPages = ($currentPage>4) ? $currentPage : 5;
                $maxPages = ($currentPage<$allPages-4) ? $currentPage : $allPages - 4;

                for($i=$minPages-4; $i<$maxPages+5; $i++) {
                    $pagination .= ($i == $currentPage) ? "<a href=\"#\"
                    class=\"current\">".$i."</a> " : "<a href=\"".$filePath."?
                    start=".(($i-1)*$limit).$otherParams."\">".$i."</a> ";
                }
                $pagination .= ($currentPage<$allPages-4) ? " ... " : " ";
            } else {
                $pagination .= " ... ";
            }
        }
    } else {
        for($i=1; $i<$allPages+1; $i++) {
        $pagination .= ($i==$currentPage) ? "<a href=\"#\" class=\"current\">".$i."</a> "
        : "<a href=\"".$filePath."?start=".(($i-1)*$limit).$otherParams."\">".$i."</a> ";
        }
    }

    if ($currentPage>1) $pagination = "<a href=\"".$filePath."?
    start=0".$otherParams."\">FIRST</a> <a href=\"".$filePath."?
    start=".(($currentPage-2)*$limit).$otherParams."\"><</a> ".$pagination;
    if ($currentPage<$allPages) $pagination .= "<a href=\"".$filePath."?
    start=".($currentPage*$limit).$otherParams."\">></a> <a href=\"".$filePath."?
    start=".(($allPages-1)*$limit).$otherParams."\">LAST</a>";

    echo '<div>' . $pagination . '</div>';
}

Now we have all the details information for store in MySQL,  So we need to write them into our database. We will create a new file called “visitor_tracking.php” and include it in every page that we want to track:

require_once('visitors_connections.php');//the file with connection code and functions
//get the required data

$visitor_ip = GetHostByName($REMOTE_ADDR);
$visitor_browser = getBrowserType();
$visitor_hour = date("h");
$visitor_minute = date("i");
$visitor_day = date("d");
$visitor_month = date("m");
$visitor_year = date("Y");
$visitor_refferer = GetHostByName($HTTP_REFERER);
$visited_page = selfURL();

//write the required data to database
mysql_select_db($database_visitors, $visitors);
$sql = "INSERT INTO visitors_table (visitor_ip, visitor_browser, visitor_hour,
 visitor_minute, visitor_date, visitor_day, visitor_month, visitor_year,
 visitor_refferer, visitor_page) VALUES ('$visitor_ip', '$visitor_browser',
 '$visitor_hour', '$visitor_minute', '$visitor_date', '$visitor_day', '$visitor_month',
 '$visitor_year', '$visitor_refferer', '$visitor_page')";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

To display information detail about visitors, we need to create a new page called “display_visits.php”

Go to create new page PHP and then copy code bellow and save it.

require_once('visitors_connections.php');//the file with connection code and functions

if ($_GET['start'] == "") $start = 0;
else $start = $_GET['start'];
$limit = 15;

$additionalQuery = "SQL_CALC_FOUND_ROWS ";

mysql_select_db($database_visitors, $visitors);
$query_visitors = "(SELECT ".$additionalQuery." * FROM visitors_table WHERE";

if ($_POST['day']!="") {
$query_visitors .= " visitor_day = '".$_POST['day']."'";
} else {
$query_visitors .= " visitor_day = ".date("d")."";

if ($_POST['month']!="") {
$query_visitors .= " AND visitor_month = '".$_POST['month']."'";
} else {
$query_visitors .= " AND visitor_month = ".date("m")."";
}

if ($_POST['year']!="") {
$query_visitors .= " AND visitor_year = '".$_POST['year']."'";
} else {
$query_visitors .= " AND visitor_year = ".date("Y")."";
}}
$query_visitors .= " LIMIT $start,$limit)";
$insert_visitors = mysql_query($query_visitors, $visitors) or die(mysql_error());
$row_visitors = mysql_fetch_assoc($insert_visitors);
$totalRows_visitors = mysql_num_rows($insert_visitors);

$nbItems = mysql_result(mysql_query("Select FOUND_ROWS() AS nbr"),0,"nbr");
if ($nbItems>($start+$limit)) $final = $start+$limit;
else $final = $nbItems;

echo '<table style="width:100%; border:1px dashed #CCC" cellpadding="3">
      <form id="form1" name="form1" method="post" action="display_visits.php">
       <tr>
        <td>day
        <select name="day" id="day">
          <option value="" selected="selected"></option>
          <option value="01">01</option>
          <option value="02">02</option>
          <option value="03">03</option>
          <option value="04">04</option>
          <option value="05">05</option>
          <option value="06">06</option>
          <option value="07">07</option>
          <option value="08">08</option>
          <option value="09">09</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
          <option value="13">13</option>
          <option value="14">14</option>
          <option value="15">15</option>
          <option value="16">16</option>
          <option value="17">17</option>
          <option value="18">18</option>
          <option value="19">19</option>
          <option value="20">20</option>
          <option value="21">21</option>
          <option value="22">22</option>
          <option value="23">23</option>
          <option value="24">24</option>
          <option value="25">25</option>
          <option value="26">26</option>
          <option value="27">27</option>
          <option value="28">28</option>
          <option value="29">29</option>
          <option value="30">30</option>
          <option value="31">31</option>
        </select></td>
        <td>Month
        <select name="month" id="month">
          <option value="" selected="selected"></option>
          <option value="1">1</option>
          <option value="2">2</option>
          <option value="3">3</option>
          <option value="4">4</option>
          <option value="5">5</option>
          <option value="6">6</option>
          <option value="7">7</option>
          <option value="8">8</option>
          <option value="9">9</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
        </select></td>
        <td>Year
        <select name="year" id="year">
          <option value="" selected="selected"></option>
          <option value="2007">2007</option>
        </select></td>
        <td><input type="submit" name="Submit" value="Submit" /></td>
        <td></td>
       </tr>';

echo '<tr>
        <td style="width:15%;border-bottom:1px solid #CCC">IP</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Browser</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Time</td>
        <td style="width:30%;border-bottom:1px solid #CCC">Refferer</td>
        <td style="width:25%;border-bottom:1px solid #CCC">Page</td>
       </tr>';

do {

echo '<tr onmouseout="this.style.backgroundColor=\'\'"
      onmouseover="this.style.backgroundColor=\'#EAFFEA\'">
        <td>'.$row_visitors['visitor_ip'].'</td>
        <td>'.$row_visitors['visitor_browser'].'</td>
        <td>'.$row_visitors['visitor_hour'].':'.$row_visitors['visitor_minute'].'</td>
        <td>'.$row_visitors['visitor_refferer'].'</td>
        <td>'.$row_visitors['visitor_page'].'</td>
       </tr>';
} while ($row_visitors = mysql_fetch_assoc($insert_visitors));
paginate($start,$limit,$nbItems,"display_visits.php","");

There’s only one small step to do and we’re ready to see some results. We need to include the following line in every page that we need to track results for:

include('visitor_tracking.php');

To see the results please call page “display_visits.php” in your browser. Ok now I’m testing it, it works fine, I hope you will find it useful somewhere in your website. Good Luck…!

  1. July 22, 2012 at 4:36 PM

    Well this looks good, but
    CREATE TABLE `visitors_table` (
    `ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `visitor_ip` VARCHAR( 32 ) NULL ,
    `visitor_browser` VARCHAR( 255 ) NULL ,
    `visitor_hour` SMALLINT( 2 ) NOT NULL DEFAULT ’00’,
    `visitor_minute` SMALLINT( 2 ) NOT NULL DEFAULT ’00’,
    `visitor_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `visitor_day` SMALLINT( 2 ) NOT NULL ,
    `visitor_month` SMALLINT( 2 ) NOT NULL ,
    `visitor_year` SMALLINT( 4 ) NOT NULL ,
    `visitor_refferer` VARCHAR( 255 ) NULL ,
    `visitor_page` VARCHAR( 255 ) NULL
    ) ENGINE = MYISAM ;

    The above code works. You need to delete ( 32 ) next to TIMESTAMP and change TYPE to ENGINE at the end.

    But Where do I need to put the first bunch of code below “Ok. We have our database ready for storing our visitors info We will need to setup a script which will store the visitor’s info in our database so let’s start writing it. We need the ip address of our visitor so we will get it using the following method.“?

  2. July 22, 2012 at 5:53 PM

    Had to change a few things till I got this working 100%. Thank you for the script!

  3. July 24, 2012 at 5:15 AM

    Thank You Buddy🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: