CODEWITHSUNDEEP
phpmysql
Fawad Ghafoor
Fawad Ghafoor

Reputation: 6217

inserting in different tables through one mysql query

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

Upvotes: 0

Views: 780

Answers (3)

Fawad Ghafoor
Fawad Ghafoor

Reputation: 6217

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.

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

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!

Upvotes: 2

Konerak
Konerak

Reputation: 39773

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

Upvotes: 1

Related Questions