At 13:10 -0500 2/4/02, Erik Price wrote: >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
Use INSERT IGNORE, or REPLACE. --------------------------------------------------------------------- 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
