Multiple inserts & error handling (logic question)

[Gorghor]

Hello everyone,

On my current web-app project, users have the ability to insert an ad with title, description, the address, linked files etc.

In SQL terms that means I'm inserting data into 5 different tables (ad, addresses, address_to_user_relation, files and log)

The way I currently proceed is linear:

1. Insert the address if specified within the form (address table)

2. Link the address to the user in the relations table

3. Get the addr_id and insert the ad itself (ad table)

4. Get the ad_id and insert links to the various files (in the files table)

5. Insert a first entry into the log

If an error occurs, I flag it and perform DELETE actions on all the concerned tables, then redirect back to the form. If not, then the user can see his freshly inserted ad.

I'm guessing there's a more logic/clever way to do this (perhaps using temporary tables? maybe something else?)
Any help here?

Also, through my research, I've become uncertain of how reliable IDs using auto_increment really are. Should I rather define these manually? and if so, what would be a good system?

Many thanks in advance,
Ludwig

[Bjom]

You can wrap the whole thing into a transaction, so you save yourself the flagging and deleting.

It depends on your actual code whether you produce issues with the auto_increment system. It usually works fine, especially when you use transactions.

[Gorghor]

Thank you Bjom, this is indeed exactly what I needed. I hadn't ever heard of this.

And yeah that also solves my concern with AI IDs. Perfect! 🙂