0

I need to insert data into two different tables in the same database using one button, but can't work out how.

My two insert commands look like this:

$insertSQL1 = sprintf("INSERT INTO table2...blah blah blah..."); // this is a whole load of info from the form
$insertSQL2 = ('INSERT INTO table2...blah blah blah...'); // this is some more data, including an array

I have tried putting them into a "BEGIN WORK... COMMIT WORK" function, as follows:

$insertSQL = "BEGIN WORK;
".$insertSQL1.";
".$insertSQL2.";
COMMIT WORK;";

...but I get always get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO...'

I can't for the life of me work out what the actual syntax error is. Any suggestions on a better way of doing this?

user1259798
  • 171
  • 1
  • 16
  • MySQL does not support INSERT ALL (like oracle, MS SQL) option to insert into multiple tables. You may use trigger for the same. – kwelsan Jan 22 '13 at 14:41

2 Answers2

1

Use can't execute multiple queries in one call (with a normal query call). Use this syntax

insert into table2 (col1, col2, col3)
values (1, 2, 3),
       (4, 5, 6)

to insert multiple records with one query.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Ah... this isn't really what I need. I'm not trying to insert multiple records into the same table; I'm trying to insert one record into one table, and another record into another table, all with just one click. Is it not possible? – user1259798 Jan 22 '13 at 14:24
  • See [here](http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples) for more info about how to do it. – juergen d Jan 22 '13 at 14:57
0

You will have to use two insert functions for that:

$query1 = "INSERT INTO table1 (col1, col2) values (1,2)";
$query2 = "INSERT INTO table2 (col1, col2) values (1,2)";
mysql_query($query1);
mysql_query($query2);

I used mysql_query for simplicity, you have to use your own DB functions for that of course. It can still be done with one click, but you need to use 2 SQL statements. Just do this:

if ($clicked==true){
   //insert queries
}
Luc
  • 331
  • 3
  • 8