inserting in different tables through one mysql query

Question

how can i insert in 3 different tables through one mysql query that is i want to write one mysql query that will insert in 3 different tables.i have basically html form in which there are different tables involved so i will insert data collected from a form into 3 different tables. is it possible if so how ?? if not why ??

for one table

  $var1_table1 = $_POST['table1_column1'];
  $var2_table1 = $_POST['table1_column2'];
  $var3_table1 = $_POST['table1_column3'];

for second table

  $var1_table2 = $_POST['table2_column1'];
  $var2_table2 = $_POST['table2_column2'];
  $var3_table2 = $_POST['table2_column3'];

for third table

  $var1_table3 = $_POST['table3_column1'];
  $var2_table3 = $POST['table3_column2'];
  $var3_table3 = $_POST['table3_column3'];

this is the formate of my variable

score 2 · Answer 1 · edited May 23 '17 at 11:48

Short answer

No you can't insert into more than one table using a single SQL statement.

Longer answer

You can achieve it by inserting into table1 and then setting up a trigger on table1 to insert data into tables 2 and 3 using a BLACKHOLE table to store your data transiently. Something like this.

Personally I would avoid this like the plague since it obscures how & where data is being inserted into the database from a given application. A more thorough discussion about this practice can be found here.

If I were you I would just write three separate INSERT statements in your php application and avoid trying to create a single query that inserts into multiple tables. It'll save you a great deal of pain!

score 1 · Answer 2 · answered Jan 05 '12 at 09:15

1

There is no one standard query that allows an insert into three tables. However:

Why do you want one query? Why not just three queries?
Maybe using a transaction can solve your problem?
Are you sure you need three different tables, if they almost represent the same data? Why not one table, and maybe three views if needed?
Should you really need this, you could do this using a stored procedure, or even a trigger

answered Jan 05 '12 at 09:15

Konerak

39,272
12
98
118

yes i need three different tables.how can i do it through transaction or stored procedure ?? – Fawad Ghafoor Jan 05 '12 at 09:18
This is the way to go. Write a transaction that inserts into the many tables. See: [START TRANSACTION, COMMIT, and ROLLBACK Syntax](http://dev.mysql.com/doc/refman/5.1/en/commit.html) – ypercubeᵀᴹ Jan 05 '12 at 09:27
And this question: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – ypercubeᵀᴹ Jan 05 '12 at 09:36

score 0 · Accepted Answer · answered Jan 05 '12 at 09:22

As far as I know, PHP's mysql_query() function doesn't support multiple queries (insert, select, update etc), and as you are inserting into different tables with different fields/columns, you have to run a separate query for each. I don't think there is a way to do what you want.

inserting in different tables through one mysql query

3 Answers3

Linked