1

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!

  • Look at http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – Khalid Dec 09 '12 at 00:24
  • Why would you even want exactly the same data in two tables. This sounds like bad DB design to me. – Philip Whitehouse Dec 09 '12 at 00:31
  • Not the exact same data. Col1 and Col2 are columns in my first table, Col3 and Col4 columns in my second table. –  Dec 09 '12 at 00:43

6 Answers6

1

You can read more about it from the MySQL Manual. In short, you cannot insert into multiple tables at once. This leaves you with three options:

  1. Multiple INSERT statements
  2. Triggers
  3. Stored Procedures

The answer to this question: MySQL Insert into multiple tables? (Database normalization?) suggests using transactions, which will not work with MyISAM, but is a good FYI if you ever switch to InnoDB.

I really recommend you read up on Triggers. They can make your life a lot easier. But if you don't want to use them, look into PHP's mysqli_multi_query, which will allow you to execute two different queries at the same time, for example:

$query = "INSERT INTO table1 (col1,col2) VALUES ('$value1','$value2');";
$query = "INSERT INTO table2 (col3,col4) VALUES ('$value3','$value4');";
$result = mysqli_multi_query($dbcon, $query);
Community
  • 1
  • 1
cegfault
  • 6,442
  • 3
  • 27
  • 49
  • Thanks for your comprehensive answer. I've got some reading up to do! –  Dec 09 '12 at 00:42
0

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).

AM-
  • 871
  • 1
  • 10
  • 20
0

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.

Nicholas Hamilton
  • 10,044
  • 6
  • 57
  • 88
0

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;
Daryl Gill
  • 5,464
  • 9
  • 36
  • 69
  • He said he's using MyISAM whch doesn't support transactions. – AM- Dec 09 '12 at 00:23
  • MyISAM does "support" transactions, in that transaction commands are parsed and then ignored. Using transaction semantics isn't WRONG with myisam, they just don't do anything, and you definitely can't roll back. Either way, OP has to use multiple inserts. – Marc B Dec 09 '12 at 00:27
0

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.

Matt Clark
  • 27,671
  • 19
  • 68
  • 123
0

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);
Yahia Fouda
  • 1
  • 2