visitor counter

homer09001 · Jan 23, 2006

is it possible to count how many people are currently browsing the website using php?

or would it require them to be registered and logged in

bpat1434 · Jan 23, 2006

Wouldn't require them to log in. There is a way. I think it couples getting the IP address they're requesting from $_SERVER['REMOTE_ADDR'], add it to a database table. Then, just update that table every so often (like each page they view) or whatever and if it's in the database, don't add it, else, add it.

Then, when you want a count, just query the database and count the number of rows (if you're doing a row-per-visitor) and count the number of rows returned.

Then, when you need to remove users from the count, just set up a cron to delete the row if the timestamp is older than 10 minutes (arbitrary time, can be set to any time-limit).

jumphopper · Jan 24, 2006

bpat1434 would it be possible to post the php code to start a count in the same column?

for example when a link is clicked..

home
1

home
2

instead of...

home
1
1
1

thanks in advance for the help!

bpat1434 · Jan 24, 2006

Well, what I was suggesting was this:

id  |      Remote_IP     |     last_page    |    lastView    |
----------------------------------------------------------------
1  |  192.168.1.2     |   index.php     |   1/23/2006 23:11:15 |
2  |  192.168.1.3     |   index2.php   |   1/23/2006 22:58:56 |

That's the table setup I was talking about...

Using the above table layout (unique key on Remote_IP column so there are no repeats), some example code:

countUsers.php

<?php

include_once('database_connection.php');  // include database params & conn var

$query = "INSERT INTO `usercount` (Remote_IP, last_page, lastView) VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$_SERVER['REQUEST_URI']."', NOW()) ON DUPLICATE UPDATE last_page='".$_SERVER['REQUEST_URI']."', lastView=NOW()";
$res = @mysql_query($query);
if(!$res){ echo mysql_error(); }

$oldest = 60*10; // Set to 10 minute maximum limit (60 seconds * 10 minutes)

$query = "SELECT count(id) AS active FROM `usercount` WHERE UNIX_TIMESTAMP(lastView)>UNIX_TIMESTAMP(NOW())-$oldest";
$res = mysql_query($query);
$users = mysql_result($res, 0, 'active');
?>

any_other_php_page.php

<?php
include_once('countUsers.php');

echo 'Currently there are '.$users.' active users online!!<br>
<span style="color: #ccc; font-size: 8px;">Based on user activity within the last 10 minutes.</span>';
?>

The above code is given as a template to have working code based upon. I do NOT guarantee its stability as working code, nor do I expect it to work prefectly.

jumphopper · Jan 24, 2006

thanks so much for the quick reply bpat1434!

one more question.
do you know if its possible to update the hits database every hour?
how would i go abouts starting that?

any help is greatly appreciated.

bpat1434 · Jan 24, 2006

Update every hour? As in "add" users who are on your site, or "delete" those that aren't on your site?

If you want the latter, it's a cron-job that runs a script like the following:

<?php
/**
 *    purge_users.php
 *
 *  Purges users from the database who are not "active"
 *  Active meaning no links clicked within last 10 minutes
 */
$query = "DELETE FROM `usercount` WHERE UNIX_TIMESTAMP(lastView)<UNIX_TIMESTAMP(NOW())-(60*10)";
$res = @mysql_query($query);

if($res)
{
  echo 'Deleted '.mysql_affected_rows($res).' users from count at: '.date('m/d/Y h:i a');
}
else
{
  echo 'Error: #'.mysql_errno().'<br>'.mysql_error();
}
?>

Just create a CronJob in your cPanel to execute this script every hour (or whatever, 10 minutes) and it will delete the rows where users aren't active. You do that, and it prints out either an error, or the number of affected rows. That output will get sent to an email address that you specify when you create the cron.

jumphopper · Jan 24, 2006

sorry about making a similar post.

my problem has nothing to do with users.
i have created a counter in my phpmyadmin database.
and all i want it to do is to automatically create a new row every hour and continue click counting in that new row.

so that i have a record of how many click-thrus my site had every hour

thanks soooo much bpat1434! youve been awesome!

bpat1434 · Jan 24, 2006

Okay, so you're counting clicks: then on each link use a similar "include" file like I had that just updates and increments the row, and the lastView column would have to match the current hour (so DATE_FORMAT(lastView, '%H')=DATE_FORMAT(NOW(), '%H'))

@:
Is your question answered? Kind of got side-tracked....

jumphopper · Jan 24, 2006

<?php
require('sql.php');
$query = "INSERT INTO counter VALUES ('','1','','m/d/Y h:i a')";
$result = mysql_query('UPDATE counter SET start = start + 1') or exit(mysql_error());
$result = mysql_query('SELECT start FROM counter') or exit(mysql_error()); 
$result = mysql_query($query);
?>

where do i add this:
$query = "CREATE INTO counter WHERE UNIX_TIMESTAMP(lastView)<UNIX_TIMESTAMP(NOW())-(60*10)";
//$res = @($query);

and can you also spot something wrong with my date?
its not posting the current time and date

bpat1434 · Jan 24, 2006

yeah... um where do you get the date? There's no mySQL date function, nor any PHP date function call...

jumphopper · Jan 24, 2006

i am a newb
please help.

bpat1434 · Jan 24, 2006

Your table should be:

CREATE TABLE `counter` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`hits` INT NOT NULL DEFAULT '0',
`timespan` DATETIME NOT NULL
) TYPE = MYISAM ;

Then, query the DB to see if you have a current row (if not, create one):

$query = "SELECT id FROM counter WHERE DATE_FORMAT(timespan, '%m/%d/%Y %H')=DATE_FORMAT(NOW(), '%m/%d/%Y %H')";
$res = @mysql_query($query);
if(!$res)
{
  $query = "INSERT INTO counter VALUES ('', '1', NOW())";
}
else
{
  $id = mysql_result($res, 0, 'id');
  $query = "UPDATE counter SET hits=hits+1 WHERE id='$id'";
}
$res = @mysql_query($query);

That should get you started....

jumphopper · Jan 24, 2006

and this would update hourly?

bpat1434 · Jan 24, 2006

No, you'd need to set up a cron-job. PHP can't execute hourly, but you can use CronJobs to execute a script hourly. The cron would look something like:

00 * * * * /home/usr/jumphopper/public_html/sub/directory/usercounter.php

That will execute the script every hour at the beginning of it.

You can read about crons here:
Webmasters Central: CronJobs

jumphopper · Jan 24, 2006

00 * * * * /home/virtual/jumphopper/fst/var/www/projects/aktest/test.php
<?php
require('sql.php');

$query = "SELECT hour FROM counter WHERE DATE_FORMAT(hour, 'm/d/Y h:i A')=DATE_FORMAT(NOW(), 'm/d/Y h:i A')"; 
$query = "INSERT INTO counter VALUES ('','1','','m/d/Y h:i A')";
$result = mysql_query('UPDATE counter SET start = start + 1') or exit(mysql_error());
$result = mysql_query('SELECT start FROM counter') or exit(mysql_error()); 
$result = mysql_query($query);

?>

what am i doing wrong? the date/time doesnt update in the mysql database.

bpat1434 · Jan 24, 2006

Do you not read what I write? Your code won't work, because it just won't. You're running 3 queries, none of which depend upon eachother, all of which do different things which assume something else....

Take this code (and only this code) and put it in test.php:

<?php
$db_host = 'localhost';
$db_user = '';
$db_pass = '';

$con = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db('counter', $con);

$sq = "SELECT id 
FROM `counter` 
WHERE MONTH(timespan)=MONTH(NOW())
	AND DAY(timespan)=DAY(NOW()) 
	AND YEAR(timespan)=YEAR(NOW())
	AND HOUR(timespan)=HOUR(NOW())";
$res = @mysql_query($sq);

if($res === FALSE)
{
	// No rows yet, we need to add the initial one
	$q = "INSERT INTO `counter` (hits, timespan) VALUES ('1', NOW())";
}
else
{
	// Already a row dealing with the current hour

// Get ID from that row:
$id = mysql_result($res, 0, 'id');
// Specify Update query
$q = "UPDATE `counter` SET hits=hits+1 WHERE id='".$id."'";
}

// Define which action we're taking (Update or Insert);
$action = substr($q, 0, strpos($q, ' ', 0));
$action = ucwords(strtolower($action));

$res = @mysql_query($q);
if(!$res)
{
	// Failed to update/insert
	die('Failed to '.$action.' value(s) in <em>counter</em>.  MySQL gave the following information:<br><strong>Error #:</strong> '.mysql_errno().'<br><strong><em>Error  :</em></strong> '.mysql_error());
}
else
{
	$cq = "SELECT * FROM `counter` ORDER BY id DESC";
	$res = @mysql_query($cq);
	// Successfully updated/inserted information
	echo 'Successfully '.$action.'ed the value(s) in <em>counter</em>.  The table output is below:<br><br>';
	echo "
<table border='1'>\n
\t<tr><th>id</th><th>hits</th><th>timespan</th></tr>\n";
	while($r = mysql_fetch_array($res))
	{
		echo "
\t<tr>\n
\t\t<td>".$r['id']."</td>\n
\t\t<td>".$r['hits']."</td>\n
\t\t<td>".$r['timespan']."</td>\n
\t</tr>\n\n";
	}
	echo "
</table>";
}

mysql_close($con);

?>

In your cPanel click on "CronJobs". Click "Advanced (Unix Style) Interface".
Make it look like:
| 00 | | | | | /home/virtual/jumphopper/fst/var/www/projects/aktest/test.php |

Then, that's it. You have to wait for the hour to roll over in order for it to update. You can run the script on its own to update manually.

EDIT
I updated the PHP code, and ran it on my server: works perfectly fine. Cron should work, but I'm not gonna test that.

jumphopper · Jan 24, 2006

i get:

 Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 4 in /home/virtual/jumphopper/fst/var/www/projects/aktest/test.php on line 20

bpat1434 · Jan 24, 2006

SOrry, had it working prior to hour change.... updated & fixed:

<?php
$db_host = 'localhost';
$db_user = '';
$db_pass = '';

$con = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db('counter', $con);

$sq = "SELECT id 
FROM `counter` 
WHERE MONTH(timespan)=MONTH(NOW())
	AND DAY(timespan)=DAY(NOW()) 
	AND YEAR(timespan)=YEAR(NOW())
	AND HOUR(timespan)=HOUR(NOW())
LIMIT 1";
$res = @mysql_query($sq);

$r = mysql_fetch_array($res);

if(!$r)
{
	// No rows yet, we need to add the initial one
	$q = "INSERT INTO `counter` (hits, timespan) VALUES ('1', NOW())";
}
else
{
	// Already a row dealing with the current hour
	// Specify Update query
	$q = "UPDATE `counter` SET hits=hits+1 WHERE id='".$r['id']."'";
}

// Define which action we're taking (Update or Insert);
$action = substr($q, 0, strpos($q, ' ', 0));
$action = ucwords(strtolower($action));

$res = @mysql_query($q);
if(!$res)
{
	// Failed to update/insert
	die('Failed to '.$action.' value(s) in <em>counter</em>.  MySQL gave the following information:<br><strong>Error #:</strong> '.mysql_errno().'<br><strong><em>Error  :</em></strong> '.mysql_error());
}
else
{
	$cq = "SELECT * FROM `counter` ORDER BY id DESC";
	$res = @mysql_query($cq);
	// Successfully updated/inserted information
	echo 'Successfully '.$action.'ed the value(s) in <em>counter</em>.  The table output is below:<br><br>';
	echo "
<table border='1'>\n
\t<tr><th>id</th><th>hits</th><th>timespan</th></tr>\n";
	while($r = mysql_fetch_array($res))
	{
		echo "
\t<tr>\n
\t\t<td>".$r['id']."</td>\n
\t\t<td>".$r['hits']."</td>\n
\t\t<td>".$r['timespan']."</td>\n
\t</tr>\n\n";
	}
	echo "
</table>";
}

mysql_close($con);

?>

jumphopper · Jan 24, 2006

wow brett you are amazing.......

jumphopper · Jan 24, 2006

do you know if its possible to get the results table to pageinate?

and how if can grab and post results from a specific timespan?
for example.

100hits 2PM-3PM 12/12/06,
96hits 4PM-5PM 12/12/06

i cant thank you enough for your help. im slowly starting to understand how php works. its starting to make sense.

also do i need to purchase a login for cPanel? i have never heard of cPanel before. i did my research. but it looks like i must subscribe

visitor counter

Hhomer09001

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

bpat1434

Jjumphopper

Jjumphopper