Insert into multiple databases using one button (PHP/MySQL)

Question

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?

MySQL does not support INSERT ALL (like oracle, MS SQL) option to insert into multiple tables. You may use trigger for the same. — kwelsan
– kwelsan, Commented Jan 22, 2013 at 14:41

juergen d · Accepted Answer · 2013-01-22 14:16:47Z

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.

answered Jan 22, 2013 at 14:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1259798 Over a year ago

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?

juergen d Over a year ago

See here for more info about how to do it.

Luc · Accepted Answer · 2013-01-22 15:02:45Z

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
}

answered Jan 22, 2013 at 15:02

Luc

3413 silver badges8 bronze badges

Collectives™ on Stack Overflow

Insert into multiple databases using one button (PHP/MySQL)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related