Adjustable visit counter

Let’s start.

First we’ll create a table logs.

Table SQL code.

CREATE TABLE `logs` (
  `logId` int(11) NOT NULL auto_increment,
  `ipAddress` varchar(39) collate utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY  (`logId`),
  KEY `ipAddress` (`ipAddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

So, our table has 3 fields. First field is logId and it’s unique key (makes rows unique). Data type is int and attribute is auto increment what means that every new row has number for 1 higher than the previous. Next we have field ipAddress that stores IP address. It has value of KEY what means it will be indexed for faster search. And last field is time that contains date and time of visit.

Now, when we have our table, we can start creating PHP code. Here is an overview of what will have to do. We’ll have 3 functions. For each of them I’ll explain what I do and why. Then we need to set up our connection to MySQL database. We’ll also have some constants that will help us generate intervals for visit periods.

At first, we’ll call function error_reporting. We pass “0” and that means we do now want any error message returned to user (in our case error with MySQL). Then we define some constants. Each constant has integer value. We’ll have 9 constants (for each period 1).

• TODAY – we use this if we want all visits what where today
• YESTERDAY – we use this if we want all visits what where yesterday
• THIS_WEEK – we use this if we want all visits what where this week
• LAST_WEEK – we use this if we want all visits what wherelast week
• THIS_MONTH – we use this if we want all visits what where this month
• LAST_MONTH – we use this if we want all visits what where last month
• THIS_YEAR – we use this if we want all visits what where this year
• LAST_YEAR – we use this if we want all visits what where last year
• ALL – we use this if we want all visits what where until now

error_reporting ( 0 );

define ( 'TODAY', 1 );
define ( 'YESTERDAY', 2 );
define ( 'THIS_WEEK', 3 );
define ( 'LAST_WEEK', 4 );
define ( 'THIS_MONTH', 5 );
define ( 'LAST_MONTH', 6 );
define ( 'THIS_YEAR', 7 );
define ( 'LAST_YEAR', 8 );
define ( 'ALL', 9 );

To make this work we need a connection with our database. I’ll not explain it in depth because almost every tutorial on internet is about connection PHP with MySQL. We use exceptions to handle errors (check tutorial Exceptions in PHP for more information).

try {
	$con = mysql_connect ( 'localhost', 'root', '' );
	if ( $con === false ) {
		throw new Exception ( 'Could not connect to database host.' );
	}
}
catch ( Exception  $e ) {
	echo $e -> getMessage();
	die();
}

try {
	mysql_select_db ( 'test', $con );
	if ( $con === false ) {
		throw new Exception ( 'Could not connect to database.' );
	}
}
catch ( Exception  $e ) {
	echo $e -> getMessage();
	die();
}

Now we can start creating our functions. First function will be getTimes and it will take argument $type that determines period. We use switch to check for which period we want to return time values. Notice that here we use our constants that we’ve defined. $time is array with  (if our period has begin and end we have 2 keys). Key from is starting time from which we retrieve our visits. Key to is time until we search. If it’s not defined we use current time as end. We use function mktime that based on values passed returns time in seconds.

function getTimes( $type ) {
	switch ( $type ) {
		case YESTERDAY : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - 1 ), date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), date ( 'd' ), date ( 'Y' ) ) - 1;
			break;
		}
		case THIS_WEEK : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) );
			break;
		}
		case LAST_WEEK : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) ); - 1;
			break;
		}
		case THIS_MONTH : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), 1, date ( 'Y' ) );
			break;
		}
		case LAST_MONTH : {
			$time ['from'] = mktime ( 0, 0, 0, ( date ( 'm' ) - 1 ), 1, date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), 1, date ( 'Y' ) ); - 1;
			break;
		}
		case THIS_YEAR : {
			$time ['from'] = mktime(0, 0, 0, 1, 1, date ( 'Y' ) );
			break;
		}
		case LAST_YEAR : {
			$time ['from'] = mktime(0, 0, 0, 1, 1, ( date ( 'Y' ) - 1 ) );
			$time ['to'] = mktime(0, 0, 0, 1, 1, date ( 'Y' ) ) - 1;
			break;
		}
		case TODAY :
		default : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), date ( 'd' ), date ( 'Y' ) );
			break;
		}
	}
	return $time;
}

As an example I’ll use case when we want visits from yesterday. Since it was before it has its starting and ending time. To get starting time we pass following values:

1. 0 – 0 hours
2. 0 – 0 minutes
3. 0 – 0 seconds
4. date( ‘m’ ) – current month
5. date( ‘d’ ) – 1 – current day and then we subtract 1 (to get yesterday)
6. date( ‘Y’ ) – current year.

With this code we get following date : 25. 4. 2008. 00:00:00 in seconds (date that script was executed is 26. 4. 2008).

Ending time we get in similar way. Those are values that we pass:

1. 0 – 0 hours
2. 0 – 0 minutes
3. 0 – 0 seconds
4. date( ‘m’ ) – current month
5. date( ‘d’ ) – current day (notice that we do not subtract 1 because our limit is today)
6. date( ‘Y’ ) – current year.

With this code we get 26. 4. 2008. 00:00:00. But since we want all visits that where yesterday we need to subtract 1 second. So we obtain 25. 4. 2008. 23:59:59. And that’s our interval. We take all visits that occurred between 25. 4. 2008. 00:00:00 and 25. 4. 2008. 23:59:59.

I think that now you know how obtain other intervals. Now we just have to return array with those values.

Next function is logVisit.

function logVisit( $con ) {
	$sql = "SELECT *
					FROM `logs`
					WHERE `ipAddress` = '" . mysql_real_escape_string ( $_SERVER ['REMOTE_ADDR'] ) . "'
						AND UNIX_TIMESTAMP(`time`) > '" . getTimes ( TODAY ) . "'
					LIMIT 1";
	try {
		if ( ( $rez = mysql_query($sql, $con ) ) === false ) {
			throw new Exception ( 'SQL code was not executed.' );
		}
	}
	catch ( Exception $e ) {
		echo $e -> getMessage();
		die();
	}
	if ( mysql_num_rows ( $rez ) === 0 ) {
		$sql = "INSERT INTO `logs`
						(
							`ipAddress`,
							`time`
						)
						VALUES (
							'" . mysql_real_escape_string ( $_SERVER ['REMOTE_ADDR'] ) . "',
							NOW()
						)";
		try {
			if ( mysql_query($sql, $con ) === false ) {
				throw new Exception ( 'SQL code was not executed.' );
			}
		}
		catch ( Exception $e ) {
			echo $e -> getMessage();
			die();
		}
	}
}

She takes 1 argument and that is database connection. Function has only one purpose, to log visits in our table. There isn’t much to explain. Again, we use exceptions. If we get error, script execution is terminated. There are 2 SQL queries. First checks if someone has visited our site today from that IP address. If that’s false, other query adds it to table.

And our last function returns number of visits for given period. She takes 2 arguments. First is database connection and second is period. Period can be any constant value .

function getStats( $con, $type = TODAY ) {
	if ( $type === ALL ) {
		$sql = "SELECT COUNT(*)
							AS `count`
						FROM `logs`";
	}
	else {
		$time = getTimes ( $type );
		$sql = "SELECT COUNT(*)
							AS `count`
						FROM `logs`
						WHERE UNIX_TIMESTAMP(`time`) > '" . $time ['from'] . "'";
		if ( isset ( $time ['to'] ) ) {
			$sql .= " AND UNIX_TIMESTAMP(`time`) < '" . $time ['to'] . "'";
		}
	}
	try {
		if ( ( $rez = mysql_query($sql, $con ) ) === false ) {
			throw new Exception ( 'SQL code was not executed.' );
		}
	}
	catch ( Exception $e ) {
		echo $e -> getMessage();
		die();
	}
	$count = mysql_fetch_assoc ( $rez );
	return $count ['count'];
}

First we have condition. If we want all visits, we use first SQL query, and if we want any other, we use second SQL query. If we do not want all visits we call our function getTimes so we can get starting and ending time. Now we check if we have ending time. If we do, just add it to SQL query. Now we try to execute the code. If we fail, script prints error and exits.

If we, for example, wanna show all visits that where last week, we do that like this.

echo getStats ( $con, LAST_WEEK );

This is our finished script. Notice one commented part at the bottom. That part is for testing your code. It inserts 100 visits in table (with random dates). If you wish, it can insert even more. When you are finished, comment it again.

try {
	$con = mysql_connect ( 'localhost', 'root', '' );
	if ( $con === false ) {
		throw new Exception ( 'Could not connect to database host.' );
	}
}
catch ( Exception  $e ) {
	echo $e -> getMessage();
	die();
}

try {
	mysql_select_db ( 'test', $con );
	if ( $con === false ) {
		throw new Exception ( 'Could not connect to database.' );
	}
}
catch ( Exception  $e ) {
	echo $e -> getMessage();
	die();
}
function getTimes( $type ) {
	switch ( $type ) {
		case YESTERDAY : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - 1 ), date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), date ( 'd' ), date ( 'Y' ) ) - 1;
			break;
		}
		case THIS_WEEK : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) );
			break;
		}
		case LAST_WEEK : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), ( date ( 'd' ) - date ( 'N' ) + 1 ), date ( 'Y' ) ); - 1;
			break;
		}
		case THIS_MONTH : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), 1, date ( 'Y' ) );
			break;
		}
		case LAST_MONTH : {
			$time ['from'] = mktime ( 0, 0, 0, ( date ( 'm' ) - 1 ), 1, date ( 'Y' ) );
			$time ['to'] = mktime(0, 0, 0, date ( 'm' ), 1, date ( 'Y' ) ); - 1;
			break;
		}
		case THIS_YEAR : {
			$time ['from'] = mktime(0, 0, 0, 1, 1, date ( 'Y' ) );
			break;
		}
		case LAST_YEAR : {
			$time ['from'] = mktime(0, 0, 0, 1, 1, ( date ( 'Y' ) - 1 ) );
			$time ['to'] = mktime(0, 0, 0, 1, 1, date ( 'Y' ) ) - 1;
			break;
		}
		case TODAY :
		default : {
			$time ['from'] = mktime(0, 0, 0, date ( 'm' ), date ( 'd' ), date ( 'Y' ) );
			break;
		}
	}
	return $time;
}

function logVisit( $con ) {
	$sql = "SELECT *
					FROM `logs`
					WHERE `ipAddress` = '" . mysql_real_escape_string ( $_SERVER ['REMOTE_ADDR'] ) . "'
						AND UNIX_TIMESTAMP(`time`) > '" . getTimes ( TODAY ) . "'
					LIMIT 1";
	try {
		if ( ( $rez = mysql_query($sql, $con ) ) === false ) {
			throw new Exception ( 'SQL code was not executed.' );
		}
	}
	catch ( Exception $e ) {
		echo $e -> getMessage();
		die();
	}
	if ( mysql_num_rows ( $rez ) === 0 ) {
		$sql = "INSERT INTO `logs`
						(
							`ipAddress`,
							`time`
						)
						VALUES (
							'" . mysql_real_escape_string ( $_SERVER ['REMOTE_ADDR'] ) . "',
							NOW()
						)";
		try {
			if ( mysql_query($sql, $con ) === false ) {
				throw new Exception ( 'SQL code was not executed.' );
			}
		}
		catch ( Exception $e ) {
			echo $e -> getMessage();
			die();
		}
	}
}

function getStats( $con, $type = TODAY ) {
	if ( $type === ALL ) {
		$sql = "SELECT COUNT(*)
							AS `count`
						FROM `logs`";
	}
	else {
		$time = getTimes ( $type );
		$sql = "SELECT COUNT(*)
							AS `count`
						FROM `logs`
						WHERE UNIX_TIMESTAMP(`time`) > '" . $time ['from'] . "'";
		if ( isset ( $time ['to'] ) ) {
			$sql .= " AND UNIX_TIMESTAMP(`time`) < '" . $time ['to'] . "'";
		}
	}
	try {
		if ( ( $rez = mysql_query($sql, $con ) ) === false ) {
			throw new Exception ( 'SQL code was not executed.' );
		}
	}
	catch ( Exception $e ) {
		echo $e -> getMessage();
		die();
	}
	$count = mysql_fetch_assoc ( $rez );
	return $count ['count'];
}

/*for ( $i = 0; $i < 100; $i++ ) {
	$time = mktime ( rand ( 0, 23 ), rand ( 0, 59 ), rand ( 0, 59 ), rand ( 1, 12 ), rand (1, 28 ), rand ( 2008, 2009 ) );
	if ( $time > time () ) {
		$i--;
		continue;
	}
	$sql = "INSERT INTO `logs`
					(
						`ipAddress`,
						`time`
					)
					VALUES (
						'" . mysql_real_escape_string ( $_SERVER ['REMOTE_ADDR'] ) . "',
						'" . date ( 'Y-m-d H:i:s', $time ) . "'
					)";
	try {
		if ( mysql_query($sql, $con ) === false ) {
			throw new Exception ( 'SQL code was not executed.' );
		}
	}
	catch ( Exception $e ) {
		echo $e -> getMessage();
	}
}*/

This will conclude our tutorial. Thanks for reading, and soon you can expect new tutorial.