Online User System

[N-Bomb(Nerd)]

Hello,

 

I'm trying to create a system where I can monitor where users are at on my website. There's no log-in system or account system where a user can register so this will be based completely off ip address. I'm wanting to have a function where I can set where a user is at so it's easier for me to specify where they are.. like this:

 

SetLocation("Account Settings");

 

I'm sure that function will be easy to make, but I can't figure out how to make the database though. Of course I know how to make the database, but I don't know the most optimal way to make the database with the correct tables and fields. I wanted to store all of the users (that ever access my website along with original time/date on view, last time viewing, current location) and I also wanted to see who is online within the last 15 minutes.. so I'm guessing I'll need an "online" table, but not sure what kind of information that would hold.

 

Any help would be appreciated.

 

Thanks.

[requinix]

I'm guessing I'll need an "online" table, but not sure what kind of information that would hold.

You answered your own question just the sentence before:

I wanted to store all of the users (that ever access my website along with original time/date on view, last time viewing, current location)

There's your table structure.

 

...Kinda. I'd make a modification though: since you can't identify a user uniquely, don't try to do any updates. Only insert data. So the table would have the IP address, location, and time.

 

Your SetLocation() would be very simple:

function SetLocation($location) {
mysql_query(sprintf("INSERT INTO table (who, where, when) VALUES ('%s', '%s', '%s')",
	mysql_real_escape_string($_SERVER["REMOTE_ADDR"]),
	mysql_real_escape_string($location),
	date("Y-m-d H:i:s")
));
}

[N-Bomb(Nerd)]

Thanks requinix. I don't know what options to put for my table though when creating it for the last seen field. Do I make it a timestamp or datetime? Also, what do I do if I want to remember all of the users history? Have another table called History and have a trigger when the online table gets updated with a new location move the old location into the history.. good idea, or bad?

 

Here's what I have thus far:

        $this->connection->multi_query("
            CREATE TABLE IF NOT EXISTS `users` (
            `id` INT NOT NULL AUTO_INCREMENT ,
            `ip_address` VARCHAR( 20 ) NOT NULL ,
            PRIMARY KEY ( `id` )
            ) TYPE = innodb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
         ");

 

I was thinking about creating another table called "online" and have an id and location and time.. Then another table called "history" and have id and location and time.. then use joins. Also, how do I make sure the ip address is unique?

[TeNDoLLA]

Btw. IP-address is not a source to be trusted. Unless you have some intranet and you know that there will be only people with static & different ip-adresses using your application.

[gizmola]

Btw. IP-address is not a source to be trusted. Unless you have some intranet and you know that there will be only people with static & different ip-adresses using your application.

 

IP Address can be trusted.  It comes directly from the IP layer and the web server. 

 

It just isn't guaranteed to work as a unique identifier, since many different people can share the same IP address, come through proxies etc.  Data that can not be "trusted" is data that comes from users and can be tampered with like referer, cookies, form input and url parameters etc. 

 

Sorry to be nitpicking, but the question of trust has a generally accepted specific meaning.  The IP address will be the one that made the tcp connection to the webserver, even though that might be very misleading when all is said and done.

[TeNDoLLA]

Yeah, well true. But thats what I meant with my post

[gizmola]

Yeah, well true. But thats what I meant with my post

 

I took it that way, just felt the need to clarify.