HelloErik, > 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.
=option 2: whilst native-MySQL will give an errmsg in response to an attempt to INSERT duplicates, PHP doesn't have to pay attention! Check out MySQL_affected_rows(). =dn --------------------------------------------------------------------- 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
