identify deliberate double entries

[Henrik gl]

I have a MySQL database collecting names, adresses etc. This is for sending free samples. I have stated that there is only one sample pr. address. But some deliberately misspell their address. Is it possible to make a script that can show adresses (and names) which look alike? Thanks for your help. (It is only possible to apply once pr. email.)

[MuSQLe]

There is a solution for your problem:

You need to customize a SELECT statement to match your search criteria; ether for e-mail adress or name or both or more...

It goes somewhat like this: SELECT name, email FROM yourdatabase WHERE email LIKE '%e%mail%'

Of course you have to customize your search parameter (where '%' is the wildcard in SQL).

Yet your sort&display problem is not solved... to display you need a PHP script which utilizes this querry-string. You have a little programming task to handle, because the script is easy to code but if you do it sloppy it will be a time-wasting process on you machine. Each entry has to be compared, this can easily get out of hand in a db with several hundrets or even thousands of entries...

Find more info at:
http://www.mysql.com/doc/en/String_c...functions.html
&
http://sqlcourse.com/select.html

[nicc777]

There is no short solution to this problem, and no matter how hard you try, there are always ways to circumvent the process.

The best I can suggest is the following:

a) User registers. Required fields is Name, Surname, Initials, Date of Birth, Address ( especially City ), Password and e-mail.

b) After registration, send an e-mail to the supplied address, from where the user must click a link to activate his/her account. This will eliminate people with bogus e-mails.

Some business rules:

1) If Surname, Initials, City and Password ( md5sum or whatever ) of one person match with another person, you can be pretty sure you are dealing with the same person. Important here are two things: a) Don't let the customer know which fields matched - they will just make sure to change it; and b) have a path of communication for people that belief they have not yet entered their details, but still want the same sample. The likelyhood for the the (b) part is very small, as it will be virtually impossible for two people with the same initials , surname and city to have the same password as well.

2) Don't send out items to an address where the account have not being activated.

3) As with nr 1 above, you can also impliment a rule where Initials, Surname, City and Date of Birth matches. You can even leave out Initials, as Surname, City and DOB match is very unlikely. Important still is to have a form where people can still request a manual process for processing their request - in the unlikely event that you do get some one with identical info as another ( based on the criteria above ).

4) Accept the fact that some people will still get around the system. Not much you can do about it. If you work on a budget, you should decide how much percentage wise you will write off for this. I belief in some way this might even be tax deductable, as it is a loss - same as theft.

Anyway - just some thoughts. There might be other ideas as well.

Cheers

[Henrik gl]

Thanks for your quick reply. I will give your idea a try - and let you know what happens.

I will hopefully soon get back

Best regards

Henrik

[Henrik gl]

Dear Nicc777

Thank for your quick reply. I will see if I can make the form entries more difficult to bypass. But as you states - they will bypass any good ideas anyway.

Best regards
Henrik