PhPmyadmin query

[Cheg]

Hello,

 

I have a table named Bob, and 2 colums titled Column 1 and Column 2. In Column 1 is the value signup_ip and Column 2 lists an ip.

 

What query do I use to get a list of all IP's that are listed more than once? I am trying to delete some duplicate users from my database.

 

Alternatively, will the following work for what I need?

SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address

If so, how do I insert my column/field names into this?

Edited May 5, 2013 by Cheg

[Cheg]

Nothing for this?

[InoBB]

Yes, the query should work as you have posted. And as for changing it to fit your on table queries I suggest reading the manual for each extension to have an exact understanding of how and why they operate.

 

HAVING - a GROUP BY extension: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

about JOINs: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

But, list is the table name, where the others are columns of the table.

 

As this being a query posted from SO, You may want to read further into the comments on that thread as they explain some details that concern performance(which really as far as my tests show, are not that drastic until you start getting over 100,000 rows of data or so).Then again, each users ISP varies on upload and download rate of data.

 

EDIT: Just remembered you could, alternatively prevent duplicates from the very beginning, something so simple it just might work.

Edited May 6, 2013 by InoBB

[Cheg]

Yes, the query should work as you have posted. And as for changing it to fit your on table queries I suggest reading the manual for each extension to have an exact understanding of how and why they operate.

 

HAVING - a GROUP BY extension: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

about JOINs: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

But, list is the table name, where the others are columns of the table.

 

As this being a query posted from SO, You may want to read further into the comments on that thread as they explain some details that concern performance(which really as far as my tests show, are not that drastic until you start getting over 100,000 rows of data or so).Then again, each users ISP varies on upload and download rate of data.

 

EDIT: Just remembered you could, alternatively prevent duplicates from the very beginning, something so simple it just might work.

 

I can't disable duplicates from the very beginning. There are a few situations, such as a user signing up with fake email addresses before realizing that won't work, where they would need to create a second account with a legit address. However, they very rarely come back, so culling some of the duplicates after time (will be the first in 2 years) is fine.

SELECT signup_ip FROM Bob
INNER JOIN (SELECT signup_ip FROM Bob
GROUP BY signup_ip HAVING count(id) > 1) dup ON Bob.signup_ip = dup.signup_ip

Is this correct? I tried reading the links, but it's like reading the law. Everything is explained in a way for experienced coders to understand, but is not friendly for us total newbies.

 

The table has 588,000 rows, so it sounds like this may be an issue. Are there any alternative, less resource intensive methods? Edit: There are about 35000 unique signup_ip values.

Edited May 6, 2013 by Cheg

[Jessica]

You realize that IP address is not a good way to identify someone right? What if two people in the same house sign up? What if someone signs up at work then at home? What if they have a dynamic IP?

[InoBB]

try this query:

SELECT signup_ip FROM Bob WHERE sigup_ip IN (SELECT signup_ip FROM Bob GROUP BY signup_ip HAVING count(signup_ip ) > 1) ORDER BY signup_ip

then try dumping the results in a loop, see what your results are. I think this may be what your looking for your query to do:

$sql = "SELECT signup_ip FROM Bob WHERE sigup_ip IN (SELECT signup_ip FROM Bob GROUP BY signup_ip HAVING count(signup_ip ) > 1) ORDER BY signup_ip";
$res = $connection->query($sql);
while ($row = $res->fetch_assoc()) {
	echo $row['signup_ip'] . "<br />";
}

This should output all IPs grouped together on duplicate.

Edited May 6, 2013 by InoBB