MySQL "Relational" Inserts

[mwd2005]

Hi,

I've run into a wall with a project I'm working on. Is there any way to perform a "Relational" MySQL insert? In other words, I have several tables in the same database for different sections of an application (i.e. Personal Contact Information, Vehicle Information, etc) which all have one field in common (which is the id field). I want to be able to insert each of the sections' data into the sections' table, but I would like to do that at the same time with one query. Is there any way to peform an insert query for multiple relational tables as you would when selecting related information from multiple tables, for example:

"INSERT INTO CONTACT_INFORMATION, VEHICLE_INFORMATION SET CONTACT_INFORMATION.id='1250', VEHICLE_INFORMATION.id='1250'"

The reason I would like to do this is so I can make sure the entire contents of the application are inserted, instead of getting partial insertions due to errors in one or more of the table structures/insert code.

Or if there is not any way to do this, is there a way to make sure everything is inserted or nothing is inserted (if it all goes in, great, or if there is a problem with any one of the inserts, give an error).

Sorry for the novel...

Thanks.

[Roger_Ramjet]

No, is the short answer. You will have to use transactions if you want to ensure all inserts succeed or fail together.

[mourisj]

You need transactions to do what you want to. Unfortunately, I don't know if newer versions of MySql support transactions.

You would then do a "commit" after your last insert, or roll back if it fails.

To achieve what you want to do, I would proceed as follows:

• Insert data in the first ("master") table, with an automatically set unique ID.
• get this unique ID (mysql_insert_id() )
• insert data in the other tables, using this unique ID
• delete the records from all tables as soon as a failure occurs (physically [delete from ...] or logically [update ... set deleted=1] delete)

However, there are several issues: You need to assert that the inserts always start from the same table: Assume you have three tables A, B and C. A user inserts data, starting at table A. The unique Id is X.
Another user also inserts data at the same time, starting at table C; his unique ID is (also) X, as it is not yet used in table C
Then, data of user A are being inserted in B (success), then in C (failure, as the unique key is violated).
The second user would prabably fail, too, depending on the rollback method chosen, the order commands are executed etc.

This problem will not happen if you guarantee that in any part of your application, inserts start in table A .

Thisa is only some kind of workaround, I fully agree with you that transactions are the better solution in this case.

JJ Mouris

[Roger_Ramjet]

" MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance. See Chapter 14, MySQL Storage Engines and Table Types. "

If you do not have the right version, or do not want to use transaction type tables then it will have to be something like JJ says. I would just add that after each insert, select back ALL the data and compare it to the original, and only procede when it matches.

The easiest way to code this will be with the queries named and coded in one section at the top of the page: insert, select and delete for each table. Then when you write your logic it will be easier to concentrate on the php code.