Hello, everyone. I have a slight dilemma, and was wondering what the standard workaround is. I have three tables: owners (auto_increment primary key is "owners_id"), objects (auto_increment primary key is "objects_id"), and owners_objects (which is a foreign key table that I created, under advice from someone on this list a while back whose email address has changed -- there are two columns in owners_objects: "owners_id" and "objects_id", and there are two unique indexes on the table, "owners_id / objects_id" and "objects_id / owners_id" -- this is to keep duplicates combinations in this table, since they would only take up extra disk space).
I am designing an application in PHP which stores the relationship between an Owner and an Object using the owners_objects table in a many-to-many relationship. When someone adds a new owner, they can choose from an HTML listbox any number of objects to associate with that owner. The PHP code creates an INSERT statement that inserts the data into "owners", and then takes the auto_incremented primary key of the last insert (which is the insert into "owners") and uses that as the value for the second INSERT statemetn: to insert into "owners_objects.owner_id". In this second INSERT statement, the "objects_id" of the Object(s) selected from the listbox go into the second column of "owners_objects". I am sure that many people have done this sort of setup. But what do you do to get around the problem of INSERTing a pair of values that already exist? Because the combinations in "owners_objects" are UNIQUE (the UNIQUE indexes), MySQL won't accept a pair that is already present. I see two possible options: 1) Check to see if the combination is already present, and if so, do not run the INSERT query 2) run the INSERT query regardless and suppress the error message The disadvantage of the first one is that it adds an extra SQL query to the process. The disadvantage of the second one is that I think it is somewhat tasteless to execute code that will knowingly error -- or should I just stop trying to be such a perfectionist? I would post code but this is all pseudocode right now b/c I haven't solved this dilemma yet -- all experimentation with this has been done from the mysql client. Thanks for your advice! Erik --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
