Search Phpmyadmin Database For Similar Entrys

So i have database with a table called users.. and in that table there is a column named IP and i want to find users with the same IP and ban them.. So how can a search the dateba

Solution 1:

You should leverage the group by query. An old blog I wrote is Understanding group bys

But basically this should work:

selectdistinct ip, count(id) from users groupby IP havingcount(id) >1

This will return all ip addresses with a count of > 1

Solution 2:

Run a SQL statement and substitute for the IP address you want to search for:

SELECT*FROM users WHERE IP ='172.0.0.1'

Solution 3:

You can start by:

SELECTcount(id) FROM tablename
GROUPBY IP

Then you just join those results back onto the table to get information about the duplicates

So modify the first statement with: SELECT count(id) as number, IP FROM tablename

Then

SELECT a.id, a.name FROM tablename a
   JOIN (
        SELECT count(id) as number, IP FROM tablename
    ) b ON a.IP = b.IP
    WHERE number > 1

This should give you all the duplicate IP addresses in the Database. You may have to figure out a way to decide on which account to keep and which to remove, but this should give you a start.