...

View Full Version : Preventing Duplicate Entries



a4udi
04-18-2009, 09:18 PM
I have a form where people can enter items into a database and then give each item a title. I would like to avoid any items having duplicate titles. How can I edit a form field to do a check first to see if there is a matching text in that mysql row and then return an error if there is? thanks!

oesxyl
04-18-2009, 09:25 PM
I have a form where people can enter items into a database and then give each item a title. I would like to avoid any items having duplicate titles. How can I edit a form field to do a check first to see if there is a matching text in that mysql row and then return an error if there is? thanks!
be more specific please, which part is a problem in what you ask, the query?
the title is given by users or you build it someway?

best regards

a4udi
04-18-2009, 10:27 PM
Sorry, I will try and clarify.

A user will go to a form and it will ask something like:

Add your story:
Title of Story ________
Number of words ________
Author ________
Category _________

If there is already a Story called "Little Red Riding Hood" in the DB I don't want them to be able to add it... so if they do, it should just say "Sorry, that title is already taken".

So is there a simple check with mySQL to look at a database table row (text) and see if there are any matches... then return an error if something already has that name?

oesxyl
04-18-2009, 11:22 PM
Sorry, I will try and clarify.

A user will go to a form and it will ask something like:

Add your story:
Title of Story ________
Number of words ________
Author ________
Category _________

If there is already a Story called "Little Red Riding Hood" in the DB I don't want them to be able to add it... so if they do, it should just say "Sorry, that title is already taken".

So is there a simple check with mySQL to look at a database table row (text) and see if there are any matches... then return an error if something already has that name?
probably something like this:


select title from story where title like 'user title input'

if the result is empty is ok else the title exists.
the bigest problem here will be to normalize the user input because same title can be with one or more spaces between words, new lines, upper, lower or mixed case for chars, diferent punctuation, and so on.

best regards

bdl
04-19-2009, 07:11 AM
the bigest problem here will be to normalize the user input because same title can be with one or more spaces between words, new lines, upper, lower or mixed case for chars, diferent punctuation, and so on.

This is going to be the real problem. You can enforce a UNIQUE constraint on the `title` field so that the user cannot enter the same title twice, but variations on the title, e.g. "Little Red Riding Hood", "Red Riding Hood", "little red riding hood", etc are all different titles that would not match and thus allow duplicates. What I'd probably do is use the UNIQUE key on the field anyway, but perform a pre-emptive SELECT statement as oesxyl recommended and perhaps use an Ajaxy form field completion script to show titles that might match as the user types, so they know it already exists in one form or another.

a4udi
04-19-2009, 12:54 PM
I'm guessing if people typed "little red riding hood" or "Little Red Riding Hood"
I could get around this duplication issue if I used string to uppercase in php when they entered data?

bdl
04-19-2009, 03:49 PM
Yes, but remember that you'll still want to represent the data in a certain way. In other words, do you want the site to display "LITTLE RED RIDING HOOD"? Or the reverse, all lower case. I doubt it. So what you might do is perform the "string to lowercase" function, and then a function that UC's the first letter of each word, so the stored data is always formatted like "Little Red...". This might not be ideal in 100% of all titles, but it would be uniform.

a4udi
04-19-2009, 04:39 PM
They are displayed in various formats, so I supposed I could store them all as uppercase and then just output it as lowercase or first word to upper.

oracleguy
04-20-2009, 09:16 AM
They are displayed in various formats, so I supposed I could store them all as uppercase and then just output it as lowercase or first word to upper.

Or store it as the user typed it but when you do the query to check if it exists, you can convert the user input and the results mysql returns into upper case.



select title from story where UCASE(title) = 'ALL UPPER CASE VERSION OF USER INPUT'


The above query is a very basic example and if the query returns a row, you know there is a conflict.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum