Geek Answers Handbook

How to insert into two tables using php with transaction?

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

now i am trying to insert into 2 tables with php using START TRANSACTIONbut does not work.

my sql query 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?

Thank you very much in advance.

+4
source share
2 answers

this is because it mysqli_querycan only process one command each time. Separate them like this:

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 use mysqli in an object oriented 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();
}

: PHP 5.5.0, $mysqli->begin_transaction(); $mysqli->autocommit(false); $mysqli->autocommit(true);

+4

if($insert1 && $insert2)

"", :

if ($insert->affected_rows > 0)
0

More articles:


All Articles