How To Create A PHP Based Hit Counter With MySQL

[iGuest 3]

websaint recently posted a PHP hit counter using a flat-file to store information.

 

This is a guide a wrote a little while ago - it was for another web site, but I'll post it here as well.

 

-----------------------------------------------------------------------------------

 

First and foremost, you need to create a new table. You can use a whole new database if you want (and assuming you have one free), but it's a bit of a waste.

 

For this guide, our table will be called 'hits', and will contain two fields - 'unique' and 'total'. The first field, 'unique', will log all unique visits/hits to the site. And 'total' will log all visits to the site, unique or not. You can do this from within PHP:

mysql_query("CREATE TABLE hits (unique int(6), total int(7))");

Or just go directly through MySQL:

CREATE TABLE hits (unique int(6), total int(7));

Now obviously, you can change the buffer size around if you want, depending on how active you are expecting your site to be. In this example, the field 'unique' can contain any number up to 6 digits long - that is to say, any number from 0 to 100,000, and 'total' can store up to 7 digits in one entry. The average person that is reading this and is interested in creating a counter from it is not going to be having astronomical numbers of hits to their site, so what I've used is probobly a bit of overkill.

 

After you've created a table, you need to construct a script that will log data to the table.

 

Because we are wanting to log unique hits, hte use of cookies is required. This obviously doesn't work for all users, but letting a few slip through the nets shouldn't really damage things.

 

<?php// Assumes: you are already connected to MySQL,// and have selected the database you will be using;// That the visitor is cookie-compatible;// That the visitor has not cleared their cookies.// Also note that this script is very optimizable.// It is intended only to work and to be easy to understand,// not to win any awards.$result = mysql_query("SELECT * FROM hits");$data = mysql_fetch_assoc($result);$total_hits = $data['total'];$unique_hits = $data['unique'];// Firstly, grab the existing data from MySQL$total_hits++;mysql_query("UPDATE hits SET total = '" . $total_hits . "'");// The total number of hits is going to be incremented// regardless of the situation, so this can be done first// to get it out of the wayif(isset($_COOKIE['visited'])) {   $cookie = $_COOKIE['visited'];   // Our global variable index will be called 'visited',   // to make it easy to remember} else {   $cookie = false;}if(!$cookie) {   $unique_hits++;   mysql_query("UPDATE hits SET unique = '" . $unique_hits . "'");   // The value of $cookie is NOT true, meaning that the user   // has not yet been counted} else {   setcookie("visited","1",time()+86400,"/");   // Now, we set the cookie's value. Here, it is set to   // expire in 86,400 seconds (24 hours) - you can   // change this is if you like, but it ensures that the   // same person won't be counted as a unique visitor   // more than once in a 24-hour period}mysql_free_result($result);mysql_close();?>

And there we have it - a fully functional hit-counting script. Now all you would need to do is save the file somewhere, and use:

<?php include("script-path.php"); ?>

Now, what if you want to retrieve the number of hits your site has received, so you can display it somewhere?

 

This is very easy to achieve. Here are a couple of custom-coded functions that'll do it for you:

 

// Assumes: You are connected to MySQL// and have selected the database// (eg. mysql_connect(), mysql_select_db())function UniqueHits() {   $result = mysql_query("SELECT unique FROM hits");   $data = mysql_fetch_assoc($result);   $unique_hits = $data['unique'];   mysql_free_result($result);   return $unique_hits;}function TotalHits() {   $result = mysql_query("SELECT total FROM hits");   $data = mysql_fetch_assoc($result);   $total_hits = $data['total'];   mysql_free_result($result);   return $total;}

Now, you could put that in a second script, and include a reference to it as well - but it would just be much easier all around to add it to the end of the other script (before the ?>).

 

Now all you have to do is:

echo UniqueHits();// ORecho TotalHits();

In order to use them.

 

-----------------------------------------------------------------------------------

 

Review:

Our MySQL table was named 'hits', and was created with:

CREATE TABLE hits (unique int(6), total int(7));

 

The final script was, including everything, was:

<?php// Assumes: you are already connected to MySQL,// and have selected the database you will be using;// That the visitor is cookie-compatible;// That the visitor has not cleared their cookies.// Also note that this script is very optimizable.// It is intended only to work and to be easy to understand,// not to win any awards.$result = mysql_query("SELECT * FROM hits");$data = mysql_fetch_assoc($result);$total_hits = $data['total'];$unique_hits = $data['unique'];// Firstly, grab the existing data from MySQL$total_hits++;mysql_query("UPDATE hits SET total = '" . $total_hits . "'");// The total number of hits is going to be incremented// regardless of the situation, so this can be done first// to get it out of the wayif(isset($_COOKIE['visited'])) {   $cookie = $_COOKIE['visited'];   // Our global variable index will be called 'visited',   // to make it easy to remember} else {   $cookie = false;}if(!$cookie) {   $unique_hits++;   mysql_query("UPDATE hits SET unique = '" . $unique_hits . "'");   // The value of $cookie is NOT true, meaning that the user   // has not yet been counted} else {   setcookie("visited","1",time()+86400,"/");   // Now, we set the cookie's value. Here, it is set to   // expire in 86,400 seconds (24 hours) - you can   // change this is if you like, but it ensures that the   // same person won't be counted as a unique visitor   // more than once in a 24-hour period}mysql_free_result($result);function UniqueHits() {   $result = mysql_query("SELECT unique FROM hits");   $data = mysql_fetch_assoc($result);   $unique_hits = $data['unique'];   mysql_free_result($result);   return $unique_hits;}function TotalHits() {   $result = mysql_query("SELECT total FROM hits");   $data = mysql_fetch_assoc($result);   $total_hits = $data['total'];   mysql_free_result($result);   return $total;}mysql_close();// Script originally created by Cloak / Xisto.com?>

And there we have it. A fully functional, ready-to-go, PHP hit-counting script that not only logs page views, but also unique visits to your site.

 

Notes:

-- All coding is done by me. As such, it is done in my own style, meaning that you might not like it.

-- This script was written 'on-the-fly' while I was writing this quick guide, and it hasn't yet been tested at all. It might contain a bug or syntax error or two.

-- You are free to use and modify this script AS YOU WISH, as long as it includes the final comment:

// Script originally created by Cloak / Xisto.com

Note that as this is a commented line (//), it will not be visible by anyone who uses this script from your site.

 

Hope you found this helpful/useful/maybe even educational.

[rockarolla 0]

only to mention, I think the word unique

mysql_query("CREATE TABLE hits (unique int(6), total int(7))");

is not allowed...

I would prefer to make the counter using session...just in case cookies aren't allowed and because using cookies is not always safe. I think they do encourage ppl to use session for cookies.
Edited February 21, 2008 by rockarolla (see edit history)

[iGuest 3]

Replying to CloakHello,I will try your counter script. I have a question though. I would like to create a visits.Html page that shows the visits to other pages. Is that possible?

Thanks in advance,Bryan

 

[iGuest 3]

Small bug in the codeHow To Create A PHP Based Hit Counter

The way the second if loop is written means the cookie is never set. Setcookie("visited","1",time()+86400,"/"); should go right after the previous mysql query.

-reply by Lauri