Page 1 of 1

Create a light-weight statistics for your site using php and MYSQL Easy to follow tutorial on creating statistical data capture, detailin Rate Topic: -----

#1 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Post icon  Posted 16 September 2008 - 02:40 AM

This tutorial will demonstrate:
1. How to set up a database.
2. How to create a table.
3. How to Select data by creating an array and using php functions to show info.
4. How to Select data using MYSQL and php.
5. How to select data by category and by date.
First set up a database in php MyAdmin for your information, call it myip. create a user granting that user all permissions and give the user a password to gain access to the data.
Open a new file calling it config.php and insert the following code into it:
<?php 

$db_name ="database_name"; 
$db_user ="user"; 
$db_pass ="password"; 
$db_host = 'localhost';
$link = mysql_connect("localhost", $db_user, $db_pass); 
if (! $link ) 
{ die ("could not connect!" . mysql_error()); 
} 
@mysql_select_db($db_name) or die("could not open $db_name: " .mysql_error()); 
?>

Replace the databasename, user, and password with your own, if you are confused about how to do this ask your host to help you.
You are now ready to set up your stats counter.
Create a table for your data by opening a file called iptable.php. Copy and paste the following code into it:
<?php
include "config1.php";
$query = "CREATE TABLE myip(id INT 
NOT NULL
AUTO_INCREMENT,
PRIMARY KEY(id), ip VARCHAR(255), filename VARCHAR(255), refer VARCHAR(255), method VARCHAR(20), system VARCHAR(255), numg INT, tmestampeds  DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL)";
print "running query";
mysql_query($query, $link) or die
("CREATE TABLE error: " . mysql_error());
mysql_close($link);
?>


Your table has an autoincrement field to automatically increment the data you insert, it has a field to capture each of the types of information needed to build your stats counter.
Firstly we need the clients ip which is the unique number of the clients computer. Secondly we capture where the client has come from, in other words which site they have come in from. Thirdly the method of access eg: PUT, GET, POST is detailed. Futhermore we catch the users system and create a timestamp for the visit which is detailed down to the last second. The numg field is optional, but it gives quick access to the number of times an ip visits your site, however in this tutorial I will demonstrate another method of selecting the same data.
The next bit is to construct the file that uses php's powerful HTTP functions to retrieve the data we need for our table, open a new file and call it count.php, copy and paste the code below into it:
<?php
include "config.php";
$filename = $_SERVER['PHP_SELF'];
$ip = trim($_SERVER['REMOTE_ADDR']); 
$ref =$_SERVER['HTTP_REFERER'];
$sys = $_SERVER['USER_AGENT'];
$met = $_SERVER['REQUEST_METHOD'];
$q = mysql_query("SELECT * FROM myip WHERE ip = '$ip'");
if (mysql_num_rows($q)  < 1) 
{ 
$num = 1; 
} 
else 
{ 
while ($row = mysql_fetch_object($q) ) 
{ 
$num = $row->numg + 1; 
} 
} 
$query = "INSERT INTO myip(ip, refer, filename, system, method, numg, tmestampeds) VALUES('$ip', '$ref', '$filename', '$sys', '$met', '$num', NOW())";
$results = mysql_query($query)
or die ("An error occured try again: $results . " . mysql_error());
mysql_close($link);
?>


This file uses the $_SERVER functions to detail the needed data, it is then inserted into the table myip using the $query variable in the code. Notice how the INSERT statement is accompanied by the mysql_query function which does the "WORK" so to speak based on the instructions given to it by INSERT.
Time to put some meat onto the bones of our system.
On any file that you wish to have statistics for on your site add the following code somewhere on the page:
<?php
include "count.php";
?>


This statement will as it says include the code in count.php to access the data from every page.
Retrieving the data in a manner that is understandable to you or is based on your needs is now a piece of cake.
To start off with the visits.php flie can be included on any page, where you wish to show the number of total visits, and the number of unique visits:
include the file below in a similar manner to the count.php file:
<?php
include "config.php";
$q = mysql_query("SELECT * FROM myip");
while($row = mysql_fetch_object($q) ) 
{ 
$var[] = $row->ip; 
} 
$total = count($var);
$unique = array_unique($var);
echo "Total visits $total";
$uniques = count($unique);
echo "Unique visits $uniques";
?>


This method of selecting data uses php rather than MYSQL to fetch the info in your table, and demonstrates that without knowing everything there is to know about SOL php can be used to work on an array created in this fashion to work with the data in any way you see fit. The array $var is manipulated here using simple php built-in functions to print out the number of total and unique users, later I will show you how to use a more complex Select statement to give you details of who visits your site daily.
To select the data from our table to show which files have been hit and how many times they have been opened use the following code, open a file call it byfilename.php and copy and paste the code into it:
<?php

include "config.php";
$results = mysql_query("SELECT COUNT(filename) as total, filename FROM myip GROUP BY filename ORDER BY total"); 

while($row = mysql_fetch_assoc($results)) 

{ 
$allhits[] = $row['filename'];

$item =$row['filename'];

$total =$row['total'];
$tot[] =$row['total'];

echo "total " . $item . " (" . $total . ")<br />"; 

} 
$tol = count($allhits);
$uni = array_sum($tot);

echo "Total hits $uni<br />";
echo "Unique pages $tol<br />";

?>


This code fetches an associative array of data from the database detailing which file was visited an how many times.
Finally lets build a little calendar system to retrieve the data on a daily basis:
open a file call it calform.php and paste the following code into it.
<form action='cal.php' method='post'>SELECT DAY</b>
Select starting date</b><br />
<select name="day">
<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>
<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="2
3">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>
<select name="month">
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option></select>
<select name="year">
<option value="2007">07</option>
<option value="2008">08</option>
<option value="2009">09</option>
<option value="2010">10</option>
<option value="2011">11</option>
<option value="2012">12</option>
<option value="2013">13</option>
<option value="2014">14</option>
<option value="2015">15</option>
<option value="2016">16</option></select><select name='type'><option value="ip">IP</option>
<option value="filename">File</option>
<option value="sys">System</option>
<option value="all">All</option></select><br /><input type="submit" value="VIEW STATS" /></form><br />


Next open a file called cal.php and paste the next bit of code into it:
<?php
include "css.php";
$newvar = $_POST['year'];
$newvar1 =  $_POST['month'];
$newvar2 =  $_POST['day'];
$type = $_POST['type'];
$vare = mktime(12, 0, 10, $newvar1, $newvar2, $newvar);
$vares = getdate($vare);
$vared = $vares['mday'];
;
$varem = $vares['mon'];
$varey = $vares['year'];
echo "<h4>$vared-$varem-$varey</h4><br />";
$dow = $vare;
$dow1 = getdate($vare);
$dow2 = $dow1['weekday'];

$array = array("0", "86400" , "172800" , "259200" , "345600" , "432000" , "518400");
foreach ($array as $ar) 
{ 
$ne1 = $vare + $ar;
$ne1d = getdate($ne1);
$monthday = "$ne1d[mday]" . ',' . "$ar";
$ne1day[] = $monthday;
$De = $ne1d['weekday'];
$D[] = "$De[0]" . "$De[1]" . "$De[2]" . " "; 
} 
$dayarray = array_combine($ne1day, $D);
foreach ($dayarray as $key => $val) 
{ 
$de = strpos($key, ',');
if ($de == 1) 
{ 
$alt = strlen($key);
$alts = $alt - 1;
$new = substr($key,2,$alts);
$thenum = substr($key, 0, 1);

} 
else 
{ 
$alt = strlen($key);
$alts = $alt - 2;
$new = substr($key,2,$alts);
$thenum = substr($key, 0, 2);

} 
echo "<td><b>$val</b></td>";
echo "<div id='cal'><a href='sday.php?day=$thenum&&ti=$vare&&add=$new&&type=$type'>$thenum</a></div>"; 
} 


?>


This file selects a 7 day period from the starting date chosen by calforms day, month year data and on selecting the link to sday.php you will be able to see your data by ip, referer, system ect.
Here is the code for sday.php:
<?php
$type = $_GET['type'];
$day = $_GET['day'];
$ti = $_GET['ti'];
$add = $_GET['add'];
 echo "$type<br />";
echo "$day<br />";
$pti = date("Y-m-d", $ti);
echo "Post time $pti<br />";
echo "add $add<br />";

?>
<?php 
if ($type == 'filename') 
{ 
$nowe = $add + $ti;
$timeecs = date("Y-m-d", $nowe);
$timd = $nowe - 86400;
$tims = date("Y-m-d", $timd);
$nom = ' 12:00:00';
$timeec = "$timeecs" . "$nom";
$tim = "$tims" . "$nom";
echo "FROM $tim";
echo "To $timeec<br />";
include "config.php";
$q = mysql_query("SELECT tmestampeds,  COUNT(filename) as total, filename FROM myip WHERE tmestampeds BETWEEN '$tim' AND '$timeec' GROUP BY filename ORDER BY total");
while ($row = mysql_fetch_assoc($q) ) 
{ 
echo "$row[filename]" . '(' . "$row[total]" . ')' . "<br />";

} 
} 
elseif ($type == 'ip') 
{ 
$nowe = $add + $ti;
$timeecs = date("Y-m-d", $nowe);
$timd = $nowe - 86400;
$tims = date("Y-m-d", $timd);
$nom = ' 12:00:00';
$timeec = "$timeecs" . "$nom";
$tim = "$tims" . "$nom";
echo "FROM $tim";
echo "To $timeec<br />";
include "config.php";
$q = mysql_query("SELECT tmestampeds,  COUNT(ip) as total, ip FROM myip WHERE tmestampeds BETWEEN '$tim' AND '$timeec' GROUP BY ip ORDER BY total");
while ($row = mysql_fetch_assoc($q) ) 
{ 
echo "$row[ip]" . '(' . "$row[total]" . ')' . "<br />";

} 
} 
elseif ($type == 'sys') 
{ 
$nowe = $add + $ti;
$timeecs = date("Y-m-d", $nowe);
$timd = $nowe - 86400;
$tims = date("Y-m-d", $timd);
$nom = ' 12:00:00';
$timeec = "$timeecs" . "$nom";
$tim = "$tims" . "$nom";
echo "FROM $tim";
echo "To $timeec<br />";
include "config.php";
$q = mysql_query("SELECT tmestampeds,  COUNT(system) as total, system FROM myip WHERE tmestampeds BETWEEN '$tim' AND '$timeec' GROUP BY system ORDER BY total");
while ($row = mysql_fetch_assoc($q) ) 
{ 
echo "$row[system]" . '(' . "$row[total]" . ')' . "<br />";

} 
} 
else 
{ 
$nowe = $add + $ti;
$timeecs = date("Y-m-d", $nowe);
$timd = $nowe - 86400;
$tims = date("Y-m-d", $timd);
$nom = ' 12:00:00';
$timeec = "$timeecs" . "$nom";
$tim = "$tims" . "$nom";
echo "FROM $tim";
echo "To $timeec<br />";
include "config.php";
$q = mysql_query("SELECT * FROM myip WHERE tmestampeds BETWEEN '$tim' AND '$timeec'");
while ($row = mysql_fetch_object($q) ) 
{ 
echo "system $row->system<br />";
echo "method $row->method<br />";
echo "ip $row->ip<br />";
echo "filename $row->filename<br />";

} 
} 
echo "<a href='calform.php'>Admin</a><br />";
?>


The little css file included in cal.php follows note this is a php not css file, call it css.php:
<?php
echo '<style type=' . "text/css" . '>
#cal { background-color: #7FFFD4;
text-align: center;
float: left;
width: 12%;
height: 10%;
padding: 1px;
margin: 1px;
font-weight: 400;
font-family: sans-serif;
font-size: 15px;
border: 2px solid #008B8B; }
h4 { font-family: Impact;
background-color: black;
padding: 15px;
font-size: 20px;
width: 50%;
font-weight: 400;
color: #7FFFD4;
text-align: center;
border: 2px solid  #7FFFD4;
 }</style>';

?>



I hope you have learned something by following this tutorial, in the next tut I will show you how to build a very simple login system to protect your data.
All code in zip form attached.

Attached File(s)

  • Attached File  sta.zip (8.16K)
    Number of downloads: 626


Is This A Good Question/Topic? 1
  • +

Replies To: Create a light-weight statistics for your site using php and MYSQL

#2 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Posted 03 February 2009 - 03:23 AM

in the file cal.php,

in the file cal.php,
line

$new = substr($key,2,$alts);
should read


if ($de == 1)
{
$alt = strlen($key);
$alts = $alt - 1;
$new = substr($key,2,$alts);
$thenum = substr($key, 0, 1);

}
else
{
$alt = strlen($key);
$alts = $alt - 2;
$new = substr($key,1,$alts);
$thenum = substr($key, 0, 2);

}

This post has been edited by kiwi2: 05 February 2009 - 01:19 AM

Was This Post Helpful? 0
  • +
  • -

#3 kOel2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-February 09

Posted 28 February 2009 - 04:41 AM

This tut was amazing ... :^: :^: :^: :^: :^:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1