Check for duplicates in a MySQL database

[Pythondesigns]

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....

[hvle]

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'";

[brown2005]

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

[wildteen88]

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.

[Barand]

[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";

[hvle]

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

[Pythondesigns]

Thanks for all the replies :D

Perfect :D

[pixy]

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