Php mysql - insert into multiple tables in one query

Question

I have below tables while adding the product

product_table [contains basic details like pid, name, rate, height, width]
product_sellers [contains param like seller details]
product_offers [contains offers on products]

and so on 5 tables. There are total 8 tables and all the tables are linked through pid as foreign key.

I know below three approaches:

Using Insert statement one by one on all tables

$sql = "table 1 insert"; $sq2 = "table 2 insert";

make functions of all tables and call them one by one.

Using BEGIN and COMMIT statements.

BEGIN
  $sql = "table 1 insert";
  $sq2 = "table 2 insert";
COMMIT

Is there any better approach and out of above 3 which approach is faster and proficient?

Your second approach: what exactly do you mean by "make functions of all tables"? — jbafford
– jbafford, Commented Jan 11, 2016 at 15:10
means make functions which has insert statements and call them by passing last_insert_id as a fuunction — Gags
– Gags, Commented Jan 11, 2016 at 15:10
check this SO question: stackoverflow.com/questions/5178697/… — Mr Heart
– Mr Heart, Commented Jan 11, 2016 at 15:13

jbafford · Accepted Answer · 2016-01-11 15:17:57Z

1

The advantage for using

    BEGIN
      $sql = "table 1 insert";
      $sq2 = "table 2 insert";
    COMMIT

is that your changes can be batched together on the server side.

Because you are making changes to multiple tables, putting all of the changes into a transaction (rather than running in autocommit) will allow you to back out of all of the changes if one of them fails. It will also allow the changes to your database to appear atomically to other clients, rather than have changes become visible as each query executes.

It is less of a factor in this case, but when multiple modifications to the same table are batched together, that may also allow for more efficient updating of indexes.

How you implement that in your PHP code is up to you. Depending on the needs of your application, one function that handles everything may be fine. But it may also be better to split the individual insert calls to the database into different functions that can be reused (especially if pieces of the product could change after being created), and have one function that handles "insert a product" by starting the transaction, calling the individual functions, handling errors, and committing or rolling back the transaction as appropriate.

answered Jan 11, 2016 at 15:17

jbafford

5,6681 gold badge27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gags Over a year ago

If i am taking function approach then i do not think that it can be reused anywhere in application. In this transaction how i can do error handle if any of Insert statement fails

jbafford Over a year ago

What you do for error handling is entirely up to you; without knowing more about your application, it's not really possible to advise in specific. Generally, your error handler would probably execute a ROLLBACK query to revert the changes that had already been started so the database is in a clean state, and return some kind of error so that your application can handle an insert failure in an appropriate manner.

Gags Over a year ago

How about using mysqli_multi_query

jbafford Over a year ago

mysqli_multi_query lets you execute multiple queries in one call to the database, but it's still basically the same as making multiple individual mysqli_query calls. It's just a performance optimization to reduce the number of round-trips to the server (but without the ability to do anything as a result of a failure of an individual query until they've all executed).

Gags Over a year ago

Yup. I got that. From above comments it feels that Begin Commit approach is best to use. Can you do me a favor by creating one transaction with Error Handling for just 2 queries as i m not quite confident in using Transactions. U cAn use sqlfiddle.com

Collectives™ on Stack Overflow

Php mysql - insert into multiple tables in one query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related