Jump to content


Photo

Check for duplicates in a MySQL database


  • Please log in to reply
7 replies to this topic

#1 Pythondesigns

Pythondesigns
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 July 2006 - 01:42 PM

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

#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 14 July 2006 - 01:48 PM

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'";
Life's too short for arguing.

#3 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 14 July 2006 - 01:49 PM

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

#4 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 14 July 2006 - 02:46 PM

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.

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 14 July 2006 - 02:57 PM

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


To get just the duplicates

"select count(*) as count, url from table group by url having count > 1";
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 15 July 2006 - 12:22 AM

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
Life's too short for arguing.

#7 Pythondesigns

Pythondesigns
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 15 July 2006 - 04:51 PM

Thanks for all the replies :D

Perfect :D

#8 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 15 July 2006 - 04:52 PM

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

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users