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:
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.
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.
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.
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.
When I get back to the office I'll post the submit code so that seems to be missing something.
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.
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.