Forum Moderators: open
Users to my site will have an identifying cookie written to their system. This will allow me to track information about the users across multiple sessions.
One thing I would like to do is to record the user's ip address.
The problem is, with dial-up and other issues, a computer with one cookie can have multiple ip addresses associated with it.
I would like to store these addresses together for easy access and reference in my table, but I don't how best to do that?
I tried playing around with enum and set, thinking these dealt with arrays, but have since learned they are not suited for my needs.
Any advice? Thanks
CREATE TABLE `userIps` (
`ip_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ip_userId_F` BIGINT UNSIGNED NOT NULL ,
`ip_ip` INT( 4 ) UNSIGNED NOT NULL ,
`ip_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
INDEX ( `ip_userId_F` , `ip_ip` , `ip_ts` )
) ENGINE = MYISAM ;
Basically, every time the IP address changes for the user you INSERT the users account primary key and the IP address (in this example converted using ip2long [php.net] or equivalent) into the userIps table:
INSERT INTO `userIps` ( `ip_id` , `ip_userId_F` , `ip_ip` , `ip_ts` )
VALUES (
NULL , '57', '1208215691',
CURRENT_TIMESTAMP
);
I would also add a `lastIPused` field to the user table so you don't have to do a lot of unnecessary queries on your userIps table. Then, when you need the IP history for a user, you just do a SELECT from the userIps table:
SELECT * FROM `userIps` WHERE `ip_userId_F` =57 ORDER BY `ip_ts` ASC
Added: It's funny you asked about this because it's actually something I forgot to implement in the project I'm working on right now so it was a perfect reminder. :)
