PHP / MySQL语法插入多个表（多对多结构）

Question

I am trying to insert data from one form into multiple MySQL tables (using MYSQL syntax, although I will be migrating the application MYSQLi at some point - this probably isn't relevant right now, but thought I'd say it to stop the "you should be doing this in MYSQLi" comments that inevitably follow - I know I should, and I will be... soon... but for a number of reasons out of my control, I can't yet, and we still need this working).

Essentially, I am inserting into a "many-to-many" table structure (i.e. instances that can be part of many categories, and categories that can include many instances). The main table Instances, has all of the main pieces of data (in this case, places on a map - for example 'Acme Cafe', 'Lakeside Picnic Area', etc); another table Categories includes all of the possible categories for all of the places in the instances table - for example, 'Cafe', 'Pub', 'View Point', etc; and a joining table Instance_Categories, connects the places in the Instances table to the categories in the Categories table.

The PHP form has the following fields:

• Reference (a 9 digit randomly generated identifier used to identify the instance with it's many categories in the Instance_Categories table)
• Name (text box)
• Address (text box)
• Phone (text box)
• Tick-box fields for each of the categories (8 of them).

The Instances table has the following columns

• ID (unique for the table)
• Reference (see form input above)
• Name
• Address
• Phone

The Categories table has the following columns

• ID (unique to the table)
• Category (varchar)
• Category_Ref (a number to identify the reference in the connecting Instances_Categories table)

The Instances_Categories table has the following columns:

• ID (unique to the table)
• Instance_Ref (from the form input above)
• Category_Ref (relating to the Categories table above)

How do I formulate the INSERT statement in order to insert the data into my database? Can I just formulate an insert command for each table and run them one after the other from the same "Submit" button? And how would I get the insert command to dynamically insert more rows into the Instances_Categories table depending on how many category tick-boxes have been selected? I'm tearing my hair out!

Answer 1

If you're wanting to insert to multiple tables with one query you can't.

You could use a transaction, but just run the multiple queries and call it a day.