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 10 of 10
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Preventing duplicate data pairs

    I've developed a system for assigning classes to a document. We have a class table which simply has an id and a name for the class. The Name field must be unique. I set this up in creating the table using PHPMyAdmin.

    Now I tie documents to a class and users to a class with a userClass table and a document class table. These are simple tables. The documentClasses table has the primary key id plus documentID and classID columns. The userClasses table has the same structure with a primary key id, userID and classID column.

    Here's the problem, to prevent duplicates from showing up in the list I have to find a way to prevent duplicate pairs of data. If I assign Unique to classID then can not assign multiple documents the same class. If I make the documentID unique I can not assign multiple classes to a document.

    What I want to prevent is a document or user being assigned to the same class twice. IOW the DocumentClasses table cannot look like this:

    documentID, classID
    1, 2
    1, 3
    1, 1
    1, 3
    3, 1
    2, 1

    If anyone has an idea of how to prevent 1, 3 from showing up twice I'd appreciate a suggestion. I have no clue where to start.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,862
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    If I assign Unique to classID then can not assign multiple documents the same class. If I make the documentID unique I can not assign multiple classes to a document.
    There's a way to include multiple column in a single index. See http://dev.mysql.com/doc/refman/5.0/...n-indexes.html
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    rgEffects (01-11-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Thanks for the suggestion. I use this kind of thing all the time but how do I prevent duplicate entries from being submitted to the form. I can't find any language that says if a and b already exist do not add the record. I can do if a exists don't add or if b exists don't add but I can't find any way to test if a and b exists don't add.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    As the link points out, you create a composite primary or unique key on both of the properties. If both are used for the primary key, than you cannot insert 2 records with the same documentID and classID, but you can insert as many records of documentID and classID together that create a unique entity.
    You can do nothing to prevent duplicates being entered if you don't know about the entries before hand (and in languages like PHP that is typically not ideal to move around with that much data at a time). Instead, you let the language accept whatever is given to it, and then you attempt to provide it to the database. It will throw an error if you attempt to insert a duplicate, and you can simply relay back saying its not unique.

    You can use AJAX if you want to detect this while they fill in the form. The logic is identical to one that were to check for a unique username, but you would give it both fields to work with instead. I know for sure you'll be able to find a script on google for an ajax username check.

  • Users who have thanked Fou-Lu for this post:

    rgEffects (01-14-2013)

  • #5
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    I figured out how to use phpMyAdmin to set up the unique index for the pair of columns. That's working just fine now, but I'd like to get the error message to show up on the same page so users won't have to go back a page to submit another value. I'm trying to avoid any java on the site but may have to resort to this option unless anyone has a better idea.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    PHP is a server side language. It is unable to respond to client events. You must submit the form prior to being able to make a determination on an error. Nothing stops you from using a self submitting form though, so you can just repopulate it with what they had and indicate the error. Otherwise you'll need to use javascript (I assume you mean javascript and not java) such as AJAX to do this. AJAX is simply a background fetch from the server, but the server side language still functions the same as it would from any request.

  • #7
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Thanks for the followup. I did mean javascript. I have other forms that check for unique fields but return the user to the same page after the error message and echo something like e-mail already in use, please try another. For some reason this time, when I submit th form I get a blank page with the following message: Duplicate entry '1-35' for key 2

    When I get back to the office I'll post the submit code so that seems to be missing something.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Depends on how you are getting it. If it comes out that nicely, I have to admit I'm a bit surprised by it. Errors are typically far more ambiguous.
    Use the mysql_errno() (or the library equivalent). If you are using mysql you can get the error codes here: http://dev.mysql.com/doc/refman/5.1/...es-server.html. Looks like errno of 1022 in MySQL dictates that its a duplicate key, so if you check the errno on there you should see 1022. Use it to your advantage when determining what to show.

  • #9
    Regular Coder
    Join Date
    Jul 2012
    Location
    London
    Posts
    473
    Thanks
    4
    Thanked 86 Times in 86 Posts
    something like this?

    Code:
    ALTER TABLE tbl1
      ADD UNIQUE KEY(col1, col2)

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by tempz View Post
    something like this?

    Code:
    ALTER TABLE tbl1
      ADD UNIQUE KEY(col1, col2)
    Same goes with PRIMARY KEY as well if you wanted that instead of just the unique. I typically name mine, but its not required.
    I don't typically use the phpMyAdmin, but I do seem to recall that using it for composite keys and relations was a nightmare. SQL is much much easier.

  • Users who have thanked Fou-Lu for this post:

    tempz (01-14-2013)


  •  

    Posting Permissions

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