Tracking Online Users

compuXP · 12-27-2005, 05:53 PM

I have a tough question, IMO.

I want to keep track of all visitors to a webpage - whether logged in or guest, and know what URL they are on.

Similar to a forum's "users online" feature... I guess that each page load, you should store the URL and IP in a database, then on the next time a page is loaded, delete entries older than 5 minutes (as we could assume they have left then) ... but I dunno how to do this?

Thanks,
-Matt

bbott · 12-27-2005, 08:07 PM

What is the purpose of this? Why don't you just instal AWStats on your server? It pretty much tells you how many visitors each page has, whether they entered or exited through that page, etc. etc.

It's not real time stats though. Check our hotscripts.com if you need something like you described.

compuXP · 12-27-2005, 11:29 PM

Bbott, perhaps you misunderstand me. I have and use AWstats (even though I use and prefer Analytics), but I want the webpage to tell me what users are where, like it says on forums:

Currently Active Users Viewing This Thread: 3 (2 members and 1 guest)

rohan2kool · 12-28-2005, 02:47 AM

It's quite simple. Don't worry, I'll make a program with PHP + MySQL for you.

The basic idea is the same as u said. it's damn simple.

compuXP · 12-28-2005, 03:11 AM

I figured it is. I just need to know the MySQL query to deal with the time.

rohan2kool · 12-30-2005, 11:41 AM

Well, there is no such query to deal with time and even if there is, I don't know it.

You can make a table with a filed as 'timestamp' instead of int, varchar etc. It's a datatype. For ex:

Code:

create table mytable(id int auto_increment, name varchar(100), time timestamp, primary key(id);

Here, whenever you enter any data in the table, the time field will automatically contain a string as it's data which denotes the time of entry. It is a 14 digit number. For ex:

20051230145858
The first 4 digits refer to the year i.e 2005
The next 2 digits refer to the month i.e 12 i.e December
The next 2 digits refer to the date i.e 30
The next 2 digits refer to the hour i.e 14 i.e 2 pm
The next 2 digits refer to the minutes i.e 58
And the last 2 refer to the seconds i.e 58

So, the time of this entry was 30th December, 2005 @ 2:58:58 p.m
To get the time(in seconds) between two activites, as in your case you the difference between current time and the time of entry of users and delete all entries older than 5 minutes, all you need to do is convert the MySQL timestamp in the no. of seconds passed since January 1, 1970 (as returned by the php function, time() ), subtract it from the current time by using the function time() and see whether the difference is greater than 300 (i.e 5 minutes). To convert do the following procedure:

Code:


$sql = "select id, time from mytable";
$row = @mysql_fetch_array($sql

while($row = mysql_fetch_array["row"]) {

	$datetime = $row["time"]; //Get the time field from the database
	$year = substr($datetime, 0, 4); //Parse values for hour, minutes etc. from the string
	$mon = substr($datetime, 4, 2);
	$day = substr($datetime, 6, 2);
	$hour = substr($datetime, 8, 2);
	$min = substr($datetime, 10, 2);
	$sec = substr($datetime, 12, 2);

	//And then, conver them to the timestamp using the mktime function

	$regTime = mkTime($hour, $min, $sec, $mon, $day, $year);

	//Get the difference in time

	$difference = time() - $regTime;

	if($difference > 300) {
		$sql  = "delete from mytable where id="; //Execute query to remove if time period is greater than 5 minutes
		$sql .= $row["id"];

		$rs = @mysql_query($sql, $conn);
	}
}

In the while loop, the array you get will be of subsequent rows as and when the loop repeats itself i.e at every iteration. Hence the check for a time difference of 5 minutes will bee conducted for every user. Now whenever a user visits the page, all you need to do is check whether the user alreay exists in the activity table and then either update or create a row with a psuedo entry, so that the time of entry is automatically entered by the MySQL server. This can be done by:

Code:

$sql  = "select * from mytable where user=";
$sql .= $username; //Where $username is the user logged in. Also, it can be 'guest' or something else if username =''

$rs = @mysql_query($sql, $conn);

if(mysql_num_rows($rs) != 0) {
	//User is already in the table. Just update the timestamp and IP(if needed)
	$sql = "update mytable set user=";
	$sql .= $username; //A psuedo entry. No actual change, just that time will be changed automatically
} else {
	//User is not in the table. Create a row
	//You can do that, most probably, so i leave it to you
}

Just do it on every page and share a common table, hence you can monitor the activity through the table. Also, you can use $REMOTE_ADDR as a variable which returns the IP address of the user.

Note: You can also manually enter time into the 'time' field, by initiating the 'time' field as a varchar(11) and filling in data returned by the time() function.

Phew... I hope my explaination and short - tutorial till now has helped you. Please give your comments on it. Thanks a lot...

compuXP · 12-30-2005, 02:20 PM

Wow, I don't have time to read it now but I will shortly.

Although, I did find this syntax in an SQL query:

... WHERE date_sub('$ts', INTERVAL 5 MINUTE) > '$ts'

Where $ts is the current timestamp. However, this doesn't seem to return any results in a SELECT query.

I'll read your tutorial shortly. Thanks

rohan2kool · 12-31-2005, 07:37 AM

well, i believe that there are two types of timestamps: 1. UNIX timestamp (a 11 digit number giving the no. of seconds elapsed since January 1, 1970) 2. timestamp datatype in SQL database(a 14 digit number represnting the year, date, month and time). I believe ur using the wrong timestamp here. Refer to my tutorial in this thread. I have discussed both these timestamps....

AidenJ · 01-02-2006, 04:52 PM

Have you tried tearing apart PHPBB or something like that to see what they are doing? I know it uses a 5 minute query. I have not gotten in depth with it, but I have expanded it to include pages on my site outside the forums proper. If I were to do it, I would probably just start there.

This is one thing I really miss about the old multi-user BBS days. I loved being able to see how many people were online and exactly what they are doing. The web really doesn't readily provide that kind of feedback, and it is something I would like to do as well.

rohan2kool · 01-03-2006, 09:15 AM

have a look at my tutorial. i made a page based on it, and well it works nicely...

loanskey · 04-08-2006, 12:27 PM

the five minutes funda is looks cool.
tell if if you are able to wright it down.
I have heard that all these stats can be counted if you use session VIA database.

I have never tried that. Like Vbulletin use it via database.

ruby · 05-29-2006, 04:02 AM

Check out the way OSC (www.oscommerce.com) handles its WHOS ONLINE feature. Its very neat and quick. I have coded my own for a couple of sites based on their design, modified to suit my own needs. Works a treat. I even identify bots crawling the site so I can tell the bots from the real users.

12-27-2005, 05:53 PM	#1
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Tracking Online Users I have a tough question, IMO. I want to keep track of all visitors to a webpage - whether logged in or guest, and know what URL they are on. Similar to a forum's "users online" feature... I guess that each page load, you should store the URL and IP in a database, then on the next time a page is loaded, delete entries older than 5 minutes (as we could assume they have left then) ... but I dunno how to do this? Thanks, -Matt __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

12-27-2005, 08:07 PM	#2
bbott Member Join Date: Dec 2005 Location: New Jersey Posts: 111	Re: Tracking Online Users What is the purpose of this? Why don't you just instal AWStats on your server? It pretty much tells you how many visitors each page has, whether they entered or exited through that page, etc. etc. It's not real time stats though. Check our hotscripts.com if you need something like you described. __________________ Storage Sheds

12-27-2005, 11:29 PM	#3
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Re: Tracking Online Users Bbott, perhaps you misunderstand me. I have and use AWstats (even though I use and prefer Analytics), but I want the webpage to tell me what users are where, like it says on forums: Currently Active Users Viewing This Thread: 3 (2 members and 1 guest) __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

12-28-2005, 02:47 AM	#4
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tracking Online Users It's quite simple. Don't worry, I'll make a program with PHP + MySQL for you. The basic idea is the same as u said. it's damn simple.

12-28-2005, 03:11 AM	#5
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Re: Tracking Online Users I figured it is. I just need to know the MySQL query to deal with the time. __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

12-30-2005, 11:41 AM	#6
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tracking Online Users Well, there is no such query to deal with time and even if there is, I don't know it. You can make a table with a filed as 'timestamp' instead of int, varchar etc. It's a datatype. For ex: Code: create table mytable(id int auto_increment, name varchar(100), time timestamp, primary key(id); Here, whenever you enter any data in the table, the time field will automatically contain a string as it's data which denotes the time of entry. It is a 14 digit number. For ex: 20051230145858 The first 4 digits refer to the year i.e 2005 The next 2 digits refer to the month i.e 12 i.e December The next 2 digits refer to the date i.e 30 The next 2 digits refer to the hour i.e 14 i.e 2 pm The next 2 digits refer to the minutes i.e 58 And the last 2 refer to the seconds i.e 58 So, the time of this entry was 30th December, 2005 @ 2:58:58 p.m To get the time(in seconds) between two activites, as in your case you the difference between current time and the time of entry of users and delete all entries older than 5 minutes, all you need to do is convert the MySQL timestamp in the no. of seconds passed since January 1, 1970 (as returned by the php function, time() ), subtract it from the current time by using the function time() and see whether the difference is greater than 300 (i.e 5 minutes). To convert do the following procedure: Code: $sql = "select id, time from mytable"; $row = @mysql_fetch_array($sql while($row = mysql_fetch_array["row"]) { $datetime = $row["time"]; //Get the time field from the database $year = substr($datetime, 0, 4); //Parse values for hour, minutes etc. from the string $mon = substr($datetime, 4, 2); $day = substr($datetime, 6, 2); $hour = substr($datetime, 8, 2); $min = substr($datetime, 10, 2); $sec = substr($datetime, 12, 2); //And then, conver them to the timestamp using the mktime function $regTime = mkTime($hour, $min, $sec, $mon, $day, $year); //Get the difference in time $difference = time() - $regTime; if($difference > 300) { $sql = "delete from mytable where id="; //Execute query to remove if time period is greater than 5 minutes $sql .= $row["id"]; $rs = @mysql_query($sql, $conn); } } In the while loop, the array you get will be of subsequent rows as and when the loop repeats itself i.e at every iteration. Hence the check for a time difference of 5 minutes will bee conducted for every user. Now whenever a user visits the page, all you need to do is check whether the user alreay exists in the activity table and then either update or create a row with a psuedo entry, so that the time of entry is automatically entered by the MySQL server. This can be done by: Code: $sql = "select * from mytable where user="; $sql .= $username; //Where $username is the user logged in. Also, it can be 'guest' or something else if username ='' $rs = @mysql_query($sql, $conn); if(mysql_num_rows($rs) != 0) { //User is already in the table. Just update the timestamp and IP(if needed) $sql = "update mytable set user="; $sql .= $username; //A psuedo entry. No actual change, just that time will be changed automatically } else { //User is not in the table. Create a row //You can do that, most probably, so i leave it to you } Just do it on every page and share a common table, hence you can monitor the activity through the table. Also, you can use $REMOTE_ADDR as a variable which returns the IP address of the user. Note: You can also manually enter time into the 'time' field, by initiating the 'time' field as a varchar(11) and filling in data returned by the time() function. Phew... I hope my explaination and short - tutorial till now has helped you. Please give your comments on it. Thanks a lot...

12-30-2005, 02:20 PM	#7
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Re: Tracking Online Users Wow, I don't have time to read it now but I will shortly. Although, I did find this syntax in an SQL query: ... WHERE date_sub('$ts', INTERVAL 5 MINUTE) > '$ts' Where $ts is the current timestamp. However, this doesn't seem to return any results in a SELECT query. I'll read your tutorial shortly. Thanks __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

12-31-2005, 07:37 AM	#8
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tracking Online Users well, i believe that there are two types of timestamps: 1. UNIX timestamp (a 11 digit number giving the no. of seconds elapsed since January 1, 1970) 2. timestamp datatype in SQL database(a 14 digit number represnting the year, date, month and time). I believe ur using the wrong timestamp here. Refer to my tutorial in this thread. I have discussed both these timestamps....

01-02-2006, 04:52 PM	#9
AidenJ Regular Member Join Date: Dec 2005 Posts: 103	Re: Tracking Online Users Have you tried tearing apart PHPBB or something like that to see what they are doing? I know it uses a 5 minute query. I have not gotten in depth with it, but I have expanded it to include pages on my site outside the forums proper. If I were to do it, I would probably just start there. This is one thing I really miss about the old multi-user BBS days. I loved being able to see how many people were online and exactly what they are doing. The web really doesn't readily provide that kind of feedback, and it is something I would like to do as well.

01-03-2006, 09:15 AM	#10
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tracking Online Users have a look at my tutorial. i made a page based on it, and well it works nicely... __________________ Eat healthy, Stay Fit, Die Anyway. --==-- If there is a man who knows everything about women, there is a Windows distribution without bugs.

04-08-2006, 12:27 PM	#11
loanskey Smurf Join Date: Mar 2006 Posts: 23	Re: Tracking Online Users the five minutes funda is looks cool. tell if if you are able to wright it down. I have heard that all these stats can be counted if you use session VIA database. I have never tried that. Like Vbulletin use it via database. __________________ ZillR : Indian social Network

05-29-2006, 04:02 AM	#12
ruby Newcomer Join Date: Feb 2006 Location: Perth, WA Posts: 29	Re: Tracking Online Users Check out the way OSC (www.oscommerce.com) handles its WHOS ONLINE feature. Its very neat and quick. I have coded my own for a couple of sites based on their design, modified to suit my own needs. Works a treat. I even identify bots crawling the site so I can tell the bots from the real users. __________________ AdToll.com... Your Tollgate to Web Traffic! Unofficial AdToll Forums

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Referral Tracking	HouseOfRock	Programming	2	07-21-2006 07:53 PM
users online	munt	Discuss iWEBTOOL	8	04-02-2006 05:40 PM
IP tracking?	dogofwar	Help and Support	4	02-08-2006 01:02 AM