Jump to content

Archived

This topic is now archived and is closed to further replies.

Pythondesigns

Check for duplicates in a MySQL database

Recommended Posts

I have a table in a MySql database like this:

id
url

I know that there are some rows which have got the same url. How can I check for these in PHP and display them....

Share this post


Link to post
Share on other sites
you can group them by url:

"select count(*) as count, url from table group by url";

returned results contains 2 column: count and url.
count contain the number of repetition of url.

you can use this result to find out the id contain repeated url:
"select id where url='repeated_url'";

Share this post


Link to post
Share on other sites
do a select

SELECT *, COUNT(*) AS NUM FROM TABLE GROUP BY NUM DESC, URL ASC

THIS WILL COUNT HOW MANY OF EACH URL THERE ARE AND SHOW U LIKE THIS

URL1      10
URL4      10
URL3       4
URL99     1

ETC, ETC...

HOPE THIS HELPS

Share this post


Link to post
Share on other sites
Also to stop duplicate URLs from being entered into the database you should set the url field to be UNIQUE which will prevent duplicate entries from being entered into the url field.

Share this post


Link to post
Share on other sites
[quote author=hvle link=topic=100562.msg396988#msg396988 date=1152884904]
you can group them by url:

"select count(*) as count, url from table group by url";

returned results contains 2 column: count and url.
count contain the number of repetition of url.

you can use this result to find out the id contain repeated url:
"select id where url='repeated_url'";
[/quote]

To get just the duplicates

"select count(*) as count, url from table group by url having count > 1";

Share this post


Link to post
Share on other sites
even better, url with most count first:

"select count(*) as count, url from table group by url having count > 1 order by count desc";

having count > 1;  I've learn new thing to day

Share this post


Link to post
Share on other sites
You could just place a UNIQUE index on the column, so that way it would be impossible to insert a duplicate record.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.