Re: INSERTing duplicate values to a UNIQUE-indexed table
I want to thank everyone for their contributions on this thread,and especially Erik for posting the question. I'm in the middle of the problem, and was about to write excessive code using Erik's suggestion No.1. Thank you, thank you to all. - Original Message - From: Erik Price [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 04, 2002 1:10 PM Subject: INSERTing duplicate values to a UNIQUE-indexed table 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 - 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 - 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
Re: INSERTing duplicate values to a UNIQUE-indexed table
I want to thank everyone for their contributions on this thread,and especially Erik for posting the question. I'm in the middle of the problem, and was about to write excessive code using Erik's suggestion No.1. Thank you, thank you to all. - Original Message - From: Erik Price [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 04, 2002 1:10 PM Subject: INSERTing duplicate values to a UNIQUE-indexed table 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 - 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
Re: INSERTing duplicate values to a UNIQUE-indexed table
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
Re: INSERTing duplicate values to a UNIQUE-indexed table
On Mon, 4 Feb 2002 at 13:10:29 -0500, Erik Price wrote: [ snip background ] 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? You can use REPLACE instead of INSERT -- see the manual entry: URL:http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#REPLACE Cheers! -- Marcus - 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
Re: INSERTing duplicate values to a UNIQUE-indexed table
On Monday, February 4, 2002, at 01:48 PM, Marcus Collins wrote: You can use REPLACE instead of INSERT -- see the manual entry: URL:http://www.mysql.com/documentation/mysql/bychapter/manual_Reference. html#REPLACE Thanks Marcus! My knowledge of MySQL is pretty basic. It has also been suggested that I use INSERT IGNORE (...) VALUES (...) -- my primitive powers of deduction tell me that this would create a tiny bit less load on the database, so I assume that this is probably the best course of action, unless I'm making a mistake about the way MySQL processes data. But now I know about both options! Much appreciated, Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] - 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
Re: INSERTing duplicate values to a UNIQUE-indexed table
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
Re: INSERTing duplicate values to a UNIQUE-indexed table
If you need to update an old record in unique key, try to use REPLACE function. REPLACE could add if there has not existing record and update when it found a same unique key. Sommai, At 19:01 5/2/2002 +, DL Neil wrote: 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 - 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
