How to insert data to several tables in MySQL?

Question

I have only just begun learning about joining tables in MySQL. Now, I have a small project where I simply want to let the visitor insert data through a form. The data is then displayed in a HTML table with four rows, joining together two tables in my database. The "problem" is that the data should be submitted into those two different tables in my database.

I tried

$query = "INSERT INTO table1, table2 (col1, col2, col3, col4) VALUES ('value1', 'value2', 'value3', 'value4')";

but that doesn't seem to do it. What is the correct syntax for submitting form data to several database tables? Oh, and I read some similar threads mentioning using transactions. Is this necessary? My tables are run with MyISAM. Thanks!

Answer 1

You can perform this by using MySQL Transactions By: Try:

BEGIN
INSERT INTO table1 (col1, col2...ETC) 
  VALUES('value1', 'value2'...ETC)
INSERT INTO table2 (col1, col2...ETC) 
  VALUES('value1', 'value2'...ETC);
COMMIT;

Answer 2

With MyISM you will need to execute the query for each table you want to insert into, I do not believe that in a single query you can add to multiple tables.

In your case you can not use Transactions because they are not supported by your engine.

Answer 3

Your only solution is to use several separate queries, preferably within a transaction. Transactions are necessary if you want to make sure that the data from each query is inserted, in which case you COMMIT the transaction; should one of the queries fail, you can ROLLBACK.

P.S. Use InnoDB. It's better in pretty much any environment where INSERT queries make up at least 5% of all queries (sadly I cannot give the source as I had read it several months ago and no longer remember where).

Answer 4

I may be wrong, but you don't insert into multiple tables at the same time. You split it into two or more commands, each handling the specific insertion, whats the big deal, that one extra line of code (which makes everything clearer) too much of a hassle to type?

Look at it this way, if you write a large script, for instance a routine to process some data, the more you segment the code, the easier it is to debug, and, if necessary, inoculate instructions that are problematic, it will end up saving you time in the long run.

Answer 5

I have this problem before You can use multiple query function

$query = "INSERT INTO table1 (col1,col2) VALUES ('$value1','$value2')";
$query = "INSERT INTO table2 (col3,col4) VALUES ('$value3','$value4')";
$result = mysqli_multi_query($dbcon, $query);