Selecting duplicate entries using php with mysql?

How do I do that?

[foy]

I'm having the following problem:

Let's assume I have a table with 3 fields: ID (type int), NAME(type varchar), EMAIL (type varchar) in a mysql db.

The fields have the following data stored:

1, jack, [e-mail]
2, tom, [e-mail]
3, jack, [e-mail]
.
.
.
.
2000, jack, [e-mail]

Now let's say I have like some thousand entries like that one above with each one containing different data. So if there are entries which have the same email adress, how I can I select exclusively those entries with the duplicate data in it?

So the result of a selection should look something like this:

(If I want to show the selection results in html):

HTML:

<table>
<tr>
<td>First matching duplicate entry.</td>
<td>Second matching duplicate entry.</</td>
</tr>
<tr>
<td>1, jack, [email][e-mail][/email]</td>
<td>2, tom, [email][e-mail][/email]</td>
</tr>
</table>

what does the php code have to look alike?

so far I got this mysql query:

SELECT id, email FROM table WHERE COUNT(email)>1

which is working fine but I don't know how I can get these results showing up with each row that has the duplicate entry.

I did this:

<?
require('dbconnect.php');
$query = "SELECT email,count(email) from table group by email having count(email) > 1";
$result = mysql_query($query);

while($row = mysql_fetch_object($result))
{
echo $row->email;
}

mysql_close();
?>

how do I get those duplicate results showing up in an html table with two columns?

[Dreamquick]

Actually I was half right before I cleared out my post... :)

If I read your question right you want to be able to return all the records where the duplicate email addresses are featured rather than just the email address. If not ignore this post.

Normally I'd do this with an IN() statement but apparently mySQL doesn't support it used in the sub-select context (which was how I love using it), so have you considered putting the data into a temporary table?

e.g.

<< create a temporary table called #mytmp holding the duplicate email addresses >>

SELECT MyTable.ID, MyTable.Name, MyTable.EMail
FROM MyTable
LEFT JOIN #mytmp ON MyTable.EMail = #mytmp.EMail
WHERE #mytmp.EMail IS NOT NULL

You should now have a result set which contains name+ID+email which you can then use the echo method to write out to the screen.

In a not-too-dissimilar scripting language I would be able to do the following (if someone who actually knows PHP wants to correct this or tell me where I went wrong feel free);

while($row = mysql_fetch_object($result)) {
?>
<tr>
<td><? echo $row->id;?></td>
<td><? echo $row->name;?></td>
<td><? echo $row->email;?></td>
</tr>
<?
}

Obviously you'd then just have to sort out a table around these cells along with other html, but if my guessed code actually works then you could use a similar method to the one it uses.

- Tony

[jatar_k]

SELECT email,count(email) from table group by email having count(email)

foy, does this work or not? I don't think it would, the order is messed up. This should work though

SELECT id,name,email FROM table WHERE COUNT(email)>1 GROUP BY email

for your display I do it a little differently

echo "<table>";
while($row = mysql_fetch_array($result))
{
?>
<tr>
<td><?= $row["id"]?></td>
<td><?= $row["name"]?></td>
<td><?= $row["email"]?></td>
</tr>
<?
}
echo "</table>";

<?= $varname?> //echos the var, just a shortcut

[foy]

great! thank you guys! works like a charm, just one last question:

how do I get matching rows coming up in different html tables?

let's say the data of row1 os matching with the data of row2 and now I want row1 to shop up in one column where the matching row2 shows up in another one, like this:

<table>
<tr>
<td>email out of row1</td>
<td>email out of row2 (this one matches with row1)</td>
</tr>
</table>

this way it would be easier for me to sort things out.

thank you

[jatar_k]

$compare1 = "none";
while($row = mysql_fetch_array($result))
{
if ($compare1!="none" && $row["email"]!=$compare1) echo "</table>";
if ($row["email"]!=$compare1) echo "<table>";
?>
<tr>
<td><?= $row["id"]?></td>
<td><?= $row["name"]?></td>
<td><?= $row["email"]?></td>
</tr>
<?
if ($row["email"]!=$compare1) $compare1 = $row["email"];
}
echo "</table>";

this was very quick so I may have screwed it up, you get my drift though.

[foy]

got it! thank you again ;)

I wonder why I didn't think about that :/