2

I have a MySQL table which contains many-to-many data about users:

username (PK)     ip (PK)          machine_id (PK)
--------------------------------------------------
tester1           1.2.3.4          ABC
tester1           6.6.6.6          BBB
tester2           1.2.3.4          ZZZ
tester3           2.3.4.5          ABC
tester4           2.3.4.5          OOO
tester5           1.2.3.4          XYZ
tester5           8.9.7.6          BBB
tester5           1.2.3.4          OOO
tester6           9.9.9.9          ZZZ

It uses a combined primary key to limit the rows to unique combinations of all three columns.

As you can see, the same user can use a combination of multiple IP addresses and multiple machines to access the system. For my purposes, it's the same user if they have the same IP or Machine ID.

I want a way to find all possible connections between the data so I can identify each possible combination of username/IP/ID someone has used.

Example

If I wanted to find out what aliases tester1 has used, it's easy: SELECT * FROM users WHERE username LIKE '%tester1%' which would give me:

tester1      1.2.3.4     ABC
tester1      6.6.6.6     BBB

I can then connect the IPs 1.2.3.4 and 6.6.6.6 with this user, and if I look up both of those IPs I can see they have used a few other names and machine IDs:

tester2      1.2.3.4     ZZZ
tester5      1.2.3.4     XYZ
tester5      1.2.3.4     OOO
tester1      6.6.6.6     BBB

I then proceed to look up all of the machine IDs for the user, which gives us one more alias of him (from machine ID OOO):

tester4      2.3.4.5     OOO

Looking up OOO I find yet another IP, 2.3.4.5, which gives me yet another connection:

tester3      2.3.4.5     ABC

Since we've looked up machine ID ABC before, there's no need to look it up again.

I've now identified all possible aliases of this user, and have a complete list of IPs, machine IDs and usernames he's used. All of this came from recursively looking up data, the results of which then had another recursive lookup performed on it, and so on.

My question is, how do I translate this logic to PHP/SQL?

Is there a way to extract all "connected" data straight via a query, or will some PHP processing be needed, and if so, what?

WackGet
  • 2,667
  • 3
  • 36
  • 50
  • mysql doesn't do recursive queries. you can "fake" it somewhat by doing a series of self-joins, but that only works for a certain "recursion" depth, it won't keep going all the way down. it also gets extraordinarily ugly very quickly. So, yes... client-side processing is required. – Marc B Jul 30 '13 at 18:41
  • I thought so. I'm just stuck on the logic of the PHP. – WackGet Jul 30 '13 at 19:09
  • basically a while() loop that just keeps digging deeper and deeper until you hit the bottom (or top) of the tree. – Marc B Jul 30 '13 at 19:10
  • You can also emulate a recursive query with a call to a recursive stored procedure (such as [the one proposed in this other anwser](http://stackoverflow.com/a/3439364/1446005)) that populates a temporary table. In theory, this should be faster than making repeated queries from PHP, but it is also a bit more painful to write. – RandomSeed Jul 30 '13 at 22:33

1 Answers1

0

Basically you can use the select query and 2 self joins on the machine id column. For example: 

      select * from users left join users u1 on users. mid=u1.mid  left join users u2 on u1. mid=u2. mid where  name like "%tester1%"
Micromega
  • 12,486
  • 7
  • 35
  • 72