Duplicate table entries search?

neljan

Hey 😉

I would like to identify (and later remove) duplicate accounts from my phpbb forum database by comparing IP addresses of all users.

Is there an easy way to do this, or do I need to sort the table via the table column and then manually compare the table values?

Thanks for your time

dagon

duplicate ip does not mean duplicate user, thousands of people could use the same ip.

neljan

dagon;10981012 wrote:
duplicate ip does not mean duplicate user, thousands of people could use the same ip.

Sorry, but I wasn't asking if users sharing the same IP = same person.

I plan to compare other data (such as usernames) and also check to see whether accounts that share the same IP are activated before deletion (some people register, forget to activate/dont recieve activation instructions [w/e] then re-register).

Thanks though...

dagon

phpbb does not allow duplicate user names, there really is no way to find duplicate users.

neljan

dagon;10981020 wrote:
phpbb does not allow duplicate user names

I said I would compare usernames to look for similarities.

For example:

Cyclops
Cyclops23

dagon;10981020 wrote:
there really is no way to find duplicate users.

There is a way to find obvious duplicate accounts by first matching IP's then looking for similarities in their usernames/email addresses/dob (etc).

Thanks.

dagon

Cyclops
Cyclops23

i wouldn't say they where obvious duplicates at all, i know several places where there is a couple of users with a name only a letter or number of my own account

laserlight

neljan wrote:
There is a way to find obvious duplicate accounts by first matching IP's then looking for similarities in their usernames/email addresses/dob (etc).

"Obvious" is subjective and a matter for debate, but the first part of what you want to do is straightforward. The second part can be achieved with functions like [man]levenshtein/man and [man]similar_text/man.

neljan

laserlight;10981080 wrote:
"Obvious" is subjective and a matter for debate, but the first part of what you want to do is straightforward. The second part can be achieved with functions like [man]levenshtein/man and [man]similar_text/man.

Hi Laserlight, thanks for actually trying to help me with my question.

Since I have no idea how to write an SQL query to filter the results to show duplicate table entries, would you mind pointing me in the right direction?

Lets say I'm looking at this:

[CODE]username | favourite_animal

How can I filter it to display:

[CODE]username | favourite_animal

John | cat
Eric | cat
Peter | canary
Sam | canary[/CODE]

Thank you.

laserlight

You could try something like:

SELECT username, favourite_animal
    FROM tablename
    WHERE favourite_animal IN (SELECT favourite_animal
                               FROM tablename
                               GROUP BY favourite_animal
                               HAVING COUNT(favourite_animal) > 1);