Storing Arrays in a MySQL table?

[Jeremy_H]

I'm wondering how I best to store an array into a MySQL table, or even if arrays would be the best approach?

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

[whoisgregg]

If you want to associate multiple IP addresses to one user account, then you would want a separate table for those IP addresses. MySQL example code below...

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. :)