how to insert into two tables using php with transaction? [duplicate]

Question

I know inserting into multiple tables with single query is not possible,

now I am trying to insert into 2 tables with php using START TRANSACTION but its not working.

my sql query is looks like

mysqli_query($con,"START TRANSACTION
    INSERT INTO users VALUES ('', '$getuser', '$getpass', '$getemail', '$fname', '$lname', '$domain', '$address1', '$address2', '$city', '$country', '$region', '$zip', '$phone', '$getplan', '$duration', '$getprice', '', '0', '0', '$code', '$date', '$time','0', '', '')
    INSERT INTO domains (username) VALUES ('$getuser') COMMIT");

So where is the problem??

many thanks in advance.

I maybe wrong, but your insert statement seems wrong to me. shouldn't it be INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);? — Harry
– Harry, Commented Aug 12, 2014 at 11:51
please read this on how to insert using correct syntax dev.mysql.com/doc/refman/5.6/en/insert.html — Harry
– Harry, Commented Aug 12, 2014 at 11:56

chresse · Accepted Answer · 2014-08-12 16:53:14Z

5

it is because mysqli_query can handle only one comand each time. Split them like:

mysqli_query($con, "SET AUTOCOMMIT=0");
mysqli_query($con,"START TRANSACTION");
$insert1 = mysqli_query($con,"INSERT INTO users VALUES ('', '$getuser', '$getpass', '$getemail', '$fname', '$lname', '$domain', '$address1', '$address2', '$city', '$country', '$region', '$zip', '$phone', '$getplan', '$duration', '$getprice', '', '0', '0', '$code', '$date', '$time','0', '', '')");
$insert2 = mysqli_query($con,"INSERT INTO domains (username) VALUES ('$getuser')");

if($insert1 && $insert2) {
    mysqli_query($con,"COMMIT");
} else {
    mysqli_query($con,"ROLLBACK");
}
mysqli_query($con, "SET AUTOCOMMIT=1");

update: if you are using mysqli the objectorientated way, you can do the following:

$mysqli->begin_transaction();
$insert1 = $mysqli->query("INSERT INTO users VALUES ('', '$getuser', '$getpass', '$getemail', '$fname', '$lname', '$domain', '$address1', '$address2', '$city', '$country', '$region', '$zip', '$phone', '$getplan', '$duration', '$getprice', '', '0', '0', '$code', '$date', '$time','0', '', '')");
$insert2 = $mysqli->query("INSERT INTO domains (username) VALUES ('$getuser')");

if($insert1 && $insert2) {
    $mysqli->commit();
} else {
    $mysqli->rollback();
}

HINT: if you use an older PHP version as 5.5.0, you can't use $mysqli->begin_transaction(); and have to use $mysqli->autocommit(false); at the begining and $mysqli->autocommit(true); at the end instead

edited Aug 12, 2014 at 16:53

answered Aug 12, 2014 at 11:53

chresse

5,8873 gold badges35 silver badges50 bronze badges

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

4 Comments

hsn0331 Over a year ago

is this mysql_query("SET AUTOCOMMIT=0"); or mysqli_query("SET AUTOCOMMIT=0"); ???

hsn0331 Over a year ago

but all of this is separate queries, not in single query. it should in 1 query with transaction. how to do that?

chresse Over a year ago

yes, but because of AUTOCOMMIT is off and START TRANSACTION they will be handled in one transaction.

hsn0331 Over a year ago

OK, if it works I will accept your answer. and if you think my question is helpful, you can upvote my question to help me increase my rating. thanks

Wojciech Glapa · Accepted Answer · 2016-01-31 18:07:38Z

0

Instead of

if($insert1 && $insert2)

name it just "insert" and then:

if ($insert->affected_rows > 0)

answered Jan 31, 2016 at 18:07

Wojciech Glapa

938 bronze badges

Collectives™ on Stack Overflow

how to insert into two tables using php with transaction? [duplicate]

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related