CODEWITHSUNDEEP
phpmysqlsqlmysqli
hsn0331
hsn0331

Reputation: 394

how to insert into two tables using php with transaction?

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.

Upvotes: 5

Views: 3030

Answers (2)

Wojciech Glapa
Wojciech Glapa

Reputation: 93

Instead of 

if($insert1 && $insert2)

name it just "insert" and then:

if ($insert->affected_rows > 0)

Upvotes: 0

chresse
chresse

Reputation: 5805

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

Upvotes: 5

Related Questions