CODEWITHSUNDEEP
phpmysqlinsert
user1259798
user1259798

Reputation: 171

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

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?

Upvotes: 0

Views: 1660

Answers (2)

Luc
Luc

Reputation: 331

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
}

Upvotes: 0

juergen d
juergen d

Reputation: 204854

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.

Upvotes: 1

Related Questions