How to prevent duplicate entries in MySQL and PHP?

MySQL: Remove similar (not necessarily exact duplicate) entries from table?

• I need to remove similar entries in a table. e.g. if there is a table of street names with x, y coordinates, then I want to remove street names that are the same name AND within close proximity. If we define 'close proximity' to be "((x2-x1)^2 + (y2-y1)^2) < 100" where (x1, y1) is the coordinate of a street, and (x2, y2) is a coordinate of another street. If the table is called 'streets' and the entries are 'street_name' and 'x' and 'y', then how can we get a list of all the similar entries based on the constraints I specified. And for more points how can I then go on to remove these entries (note that I do need to see what is deleted so I can run them through quality control to see if my 'similarity' algorithm is within the correct tolerances). The reason similarities need to be removed is because sometimes streets are erroneously entered more than once, when in fact they are the same street split in to two parts say. Thanks!

• Answer:

  Assume a column named, "id", which uniquely identifies each row, and also happens to ascend in the order entered (e.g., id=100 was entered before id=200) altho that's not really important. Display: select s1.street_name from street s1 where s1.id > (select s2.id from street where s1.street_name = s2.street_name and ((s2.x-s1.x)^2 + (s2.y-s1.y)^2) < 100 ) Remove: delete street s1 where s1.id > (select s2.id from street where s1.street_name = s2.street_name and ((s2.x-s1.x)^2 + (s2.y-s1.y)^2) < 100 ) TEST IT ON NON-CRITICAL DATA FIRST, it might not work the way I think. EDIT Re: Additional Details You're asking it to find every row where ID is in a set where it's not equal to itself. Try this: select s1.id, s1.street_name, s2.id, s2.street_name from street_name_positions s1 join street_name_positions s2 on s1.street_name = s2.street_name and (formula for calculating position means they're close) and s1.id <> s2.id Try adding some dummy data that you know ought to come up, just in case it just so happens you don't have any duplicate street names at the same location (that is possible).