I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site.
The table structure is:
CREATE TABLE `user_hashes` ( `user_id` int(11) NOT NULL, `hash` varchar(32) NOT NULL, `hash_datetime` datetime NOT NULL, UNIQUE KEY `user_id` (`user_id`,`hash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The purpose of this is to allow me to track which user accounts are used by the same person, or at least from the same computer.
I need to write a query that accepts a user_id and finds all other user_id's with matching hashes.
So far my query is as follows:
SELECT DISTINCT users.* FROM user_hashes AS a LEFT JOIN user_hashes AS b ON a.hash = b.hash LEFT JOIN users on b.user_id = users.id WHERE a.user_id = ? ORDER BY users.id
This works for all hashes matched to the user_id in the where clause but it is possible to also match against hashes related to the matched user_id's?
I could write a loop in PHP that keep running queries against the list of matched users until there are no more unique matches left but is there a nice SQL way to achieve the same result?