Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Preventing Duplicate Entries

    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!

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by a4udi View Post
    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

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by a4udi View Post
    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:
    Code:
    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

  • #5
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by oesxyl View Post
    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.

  • #6
    Regular Coder
    Join Date
    Jan 2005
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  • #7
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Jan 2005
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by a4udi View Post
    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.

    Code:
    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.
    OracleGuy


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •