Printable View
I have a PHP page that connects to an Access Database. Basically, the user enters text on the page and the info is entered ito the database once the submit button is pressed. The question "how do I remove duplicates?" is asked so often in the SQL Server newsgroups, that I think there needs to be more recommendations in database articles and books regarding the importance of data integrity, and how to protect your data through primary keys and unique constraints.
This is a bit more complex, but only requires one mysql_query and no other logic. I am trying to create a function that will check the database to see if the values entered into the text boxes are the same as existing values already in the database, and if so, display a message telling the user that this value already exists, and that they will need to enter a different one.
You should setup your email column to be a unique index. A query to insert a duplicate will fail and will return a specific mysql_errno() value. if your table already has a PRIMARY KEY, you might have other columns that need to be unique. Maybe you don't want them to be part of the PRIMARY KEY. You will need to experiment (echo mysql_errno()) to find out what that value is. the database for same entrys, check the return values and give out some error message to the user You can then test after you execute the insert query and if you get that mysql_errno() value, you know that the email already existed and the visitor can proceed.
That depends on your definition of 'good practice'. The UNIQUE-constraint exists to prevent double entries. if you can't add a PRIMARY KEY, UNIQUE CONSTRAINT, or UNIQUE INDEX to the table, then you can check manually to make sure the value you are *about* to insert does not already exist. Thus that's what you should rely on. Your query works, but in my opinion; using the UNIQUE-constraint is the only way. Prevents double entries when entering data directly in the database using a database administration tool too.