homepage Welcome to WebmasterWorld Guest from 23.20.28.193
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Selecting duplicate entries using php with mysql?
How do I do that?
foy




msg:1278126
 4:54 pm on Oct 29, 2002 (gmt 0)

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, jack@jack.com
2, tom, jack@jack.com
3, jack, jack@jack.com
.
.
.
.
2000, jack, jack@jack.com

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]jack@jack.com[/email]</td>
<td>2, tom, [email]jack@jack.com[/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




msg:1278127
 5:06 pm on Oct 29, 2002 (gmt 0)

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




msg:1278128
 5:53 pm on Oct 29, 2002 (gmt 0)

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




msg:1278129
 7:49 pm on Oct 29, 2002 (gmt 0)

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




msg:1278130
 8:04 pm on Oct 29, 2002 (gmt 0)

$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




msg:1278131
 9:07 pm on Oct 29, 2002 (gmt 0)

got it! thank you again ;)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved