PHP MySQL inserting data to multiple tables

Question

Hi, I'm trying to make an experimental web application which minimises redundant data. I have three example tables set up like so:

Table one
ID | created_at (unix timestamp) | updated_at (unix timestamp)

Table two
ID | Foreign Key to table one | Title

Table three (pages)
ID | Foreign Keys to both table one and two | Content | Metadata

The idea being that everything created in the application will have a creation/edit time.

Many (but not all) things will have a title (For example a page or a section for a page to go into).

Finally, some things will have attributes specific to themselves, eg content and metadata for a page.

I'm trying to work out the best way to enter data into multiple tables. I know I could do multiple insert queries from PHP, keep track of rows created in the current transaction and delete those rows should a later part of the transaction fail. However, if the PHP script dies completely, it may stop before all of the deletions can be completed.

Does MySQL have any inbuilt logic which would allow the insert query to be split up? Would a trigger be able to handle this type of transaction or is it beyond its capabilities?

Any advice, thoughts or ideas would be greatly appreciated.

Thanks!

Answer 1

For multiple inserts you can create a procedure and on PHP you call the procedure.

Answer 2

A solution would be to use Transactions, which allow to get "all or nothing" behaviour.

The idea is the following :

• you start a transaction
• you do your inserts/updates
• if everything is OK, you commit the transaction ; which will save everything you did during this transaction
• if not, you rollback the transaction ; and everything you did in it will be cancelled.
• if you don't commit and disconnected (if your PHP script dies, for instance), nothing will be commited, and what you did during the un-commited transaction will automatically be rolled-back.

For more informations, you can take a look at 12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax, for MySQL.

Note that transactions are only available for some DB engines :

• MyISAM doesn't support transactions
• InnoDB does (it also supports foreign keys, for instance -- it's far more advanced that MyISAM).