IP Check issue

[EchoFool]

Hey,

 

 

I use this query to check if a user has a smiliar IP to another user... yet it doesn't work... me and my friends ip's have a match but the query returns 0 suggesting there was no IP match....

 

Can some one explain what i got wrong?

 

SELECT t1.IP 
     FROM iplogs t1
           INNER JOIN iplogs t2 ON t1.IP = t2.IP 
      WHERE t1.userID='$UserID1' AND t2.UserID='$UserID2' 
LIMIT 1

 

Table Example:

 

RecordIDIPUserID

111

112

[TeddyKiller]

In that query, there is nothing relating to ip's ?

[EchoFool]

For this example situation i simply i replaced IP's with basic integers as in table example.

 

It is meant to inner join on IP field = IP field which would make two IP's match thus the two users have an ip similarity.

[F1Fan]

Are you storing each of the four IP octets as four different columns in your table?

 

If so, try this:

SELECT t1.IP
FROM iplogs t1
WHERE t1.userID='$UserID1' t1.IP = (
  SELECT t2.IP
  FROM iplogs t2
  WHERE t2.UserID='$UserID2'
  LIMIT 1
)

[EchoFool]

No, the IP field would hold for example 127.0.0.1 etc the entire IP

 

Such as:

 

Table Fields:

Row 1:

RecordID  1

IP  127.0.0.1

UserID 1

 

Row 2:

RecordID 2

ID 127.0.0.1

UserID 2

 

Row 3:

RecordID 3

ID 127.127.127.127

UserID 1

 

[F1Fan]

If that's the case, then these queries will never return results unless the IPs are identical. Are you trying to see if they are similar as in 127.0.0.1 and 127.0.0.2 are close? If so, you'll have to use a function to split the IPs up, so it is only comparing 127.0.0 to 127.0.0.

[Andy-H]

Why are you using a join? I was under the impression a join was used to create a query using records from multiple tables...

 

"SELECT COUNT(*) FROM iplogs WHERE UserID=" . $UserID1 . " ORDER BY RecordID DESC"

 

If count is greater than 1, there are multiple users on that IP...

 

//EDIT

Typo

[EchoFool]

Andy-H no that doesn't really do what im looking for..

 

@ F1Fan

 

If you look at the above rows examples :

 

Table Fields:

Row 1:

RecordID  1

IP  127.0.0.1

UserID 1

 

Row 2:

RecordID 2

ID 127.0.0.1

UserID 2

 

There 2 match on IP and so should return 1

 

Because the where clause is checking if 2 users having a matching IPs at least once in the list of rows:

 

      WHERE t1.userID='$UserID1' AND t2.UserID='$UserID2'

[andrewgauger]

SELECT t1.IP

    FROM iplogs t1

          INNER JOIN iplogs t2 ON t1.IP = t2.IP

      WHERE t1.userID='$UserID1' AND t2.UserID='$UserID2'

 

You are throwing userIDs at a query that is attempting to find duplicate ips.  You should run a different query, such that:

 

SELECT t1.IP
   FROM iplogs t1
       INNERJOIN iplogs t2 USING IP
   WHERE t1.userID != t2.userID

[andrewgauger]

Got an error on syntax with that one,

 

SELECT t1.IP
FROM iplogs t1
INNER JOIN iplogs t2 ON ( t1.IP = t2.IP ) 
WHERE t1.userID != t2.userID

 

And I put this in:

 

SELECT t1.IP
FROM iplogs t1
INNER JOIN iplogs t2 ON ( t1.IP = t2.IP ) 
WHERE t1.userID =1
AND t2.userID =2

 

And I got IP=1

 

So I think the problem was with variables.

[F1Fan]

SELECT t1.IP FROM iplogs t1 WHERE t1.userID = '$userID1' AND t1.IP IN (SELECT t2.IP FROM iplogs t2 WHERE t2.userID = '$userID2')

 

Try this. The nested select will return all rows for $userUD2, and if there is a match to $userID1's IP, the "IN" will catch it.

[EchoFool]

Did it still return one when u had 3 rows in your table like mine does ?

[F1Fan]

Is that question directed at me? If so, I am not testing the queries I suggest. I don't have a table set up like yours.

 

Did you try it?

[EchoFool]

Was aimed at andrewgauger - sorry

 

I think he was correct about the variables being the problem. I am testing it out now to see if its working - it appears to be

 

Thanks

[andrewgauger]

my table had 2 rows:

recordID            IP            UserID

1                        1              1

1                        1              2