Mysql SELECT ip address problem

[andy75180]

Hi ya,

 

Got a problem I'm trying to solve with retrieving data from my mysql database. Wondered if anyone could help.

 

Have a login system with my chess site that logs each player's username and ip when they login to play. I need a query that select only entries that have an IP address associated with 2 or more usernames. This will help me track down cheating users that have 2 or more accounts.

 

Here's an example table:-

 

--USERNAME-----IP ADDRESS---

 

--terry-------123.123.123.123--

--tony-------65.33.22.43-------

--larry-------185.43.5.67-------

--larry-------185.43.5.67-------

--paul--------32.23.0.4--------

--andy-------123.123.123.123--

--zippy-------55.56.34.123-----

 

The correct mysql select query would retrieve the ip address 123.123.123.123 because terry and andy are using it. 185.43.5.67 would not be retrieved because only larry is using it which is okay.

 

Any ideas anyone on the select query I need to submit?

 

 

 

 

 

[Maq]

I didn't test this but it should work.

 

SELECT ip_address ,count(*) AS n
FROM table
GROUP BY username
HAVING n > 1

[andy75180]

No I'm afraid that doesn't work correctly.

 

I'm just getting a list of all the ip addresses and usernames.

 

[Mchl]

You're aware that it's very common for several computers to have same public IP?

[andy75180]

Yes I know. It's only a basic security thing, so I can take a closer eye on players with the same ip.

 

I can then take further steps.

[Mchl]

Ok then.

How about this?

 

SELECT * FROM table WHERE ip IN (
SELECT ip FROM table AS t1 CROSS JOIN table AS t2 USING (ip) WHERE t1.username != t2.username GROUP BY ip)

[kickstart]

Hi

 

My attempt.

 

SELECT DISTINCT USERNAME
FROM TestUserIp
WHERE ipaddress
IN (

SELECT DISTINCT ipaddress
FROM TestUserIp a
WHERE (

SELECT count( * ) AS n
FROM TestUserIp b
WHERE b.ipaddress = a.ipaddress
) >=2
)

 

This will give you a list of the user names who have used ip addresses used by more than one user

 

All the best

 

Keith

[kickstart]

Hi

 

Mchls way of doing it is nice and tidy and I like it, although I think he missed a where clause:-

 

SELECT * FROM table WHERE ip IN (
SELECT ip FROM table AS t1 CROSS JOIN table AS t2 USING (ip) WHERE t1.username != t2.username AND t1.ip = t2.ip GROUP BY ip)

 

All the best

 

Keith

[Mchl]

This

AND t1.ip = t2.ip

is not necessary, becasue of

USING (ip)

[kickstart]

Hi

 

Interesting. Sorry. I normally use "ON" syntax and forget about the alternatives.

 

All the best

 

Keith

[fenway]

USING is not "equivalent" to ON... there are some subtle differences depending on version that may or may not matter.