Transactional statements with mysqli

Question

What is the proper syntax for preparing a transactional statement in mysqli? I've tried a number closest I've come is:

$conn->begin_transaction();
$stmt = $conn->prepare("INSERT INTO ARCHIVE_CLICKS_PATH SELECT * FROM CLICKS_PATH WHERE REFERER = ?");
$stmt->bind_param('i', $referer);
$stmt = $conn->prepare("DELETE FROM CLICKS_PATH WHERE REFERER = ?;");
$stmt->bind_param('i', $referer);
$stmt->close();
$conn->commit();

Doesn't throw errors, also doesn't seem to do anything.

Edit: I searched \ read the answer posted above before posting, it doesn't help me at all with prepared mysqli statement syntax specifically (which seems to be the issue). There is no errors being thrown, and the statement works fine when I directly input it to the database. I can get it to work fine unprepared, but I can't find the proper syntax of where to bind_params \ execute \ commit for a mysqli prepared statement example anywhere.

To add the following works fine:

BEGIN;
INSERT INTO ARCHIVE_CLICKS_PATH SELECT * FROM CLICKS_PATH WHERE REFERER = 15;
DELETE FROM CLICKS_PATH WHERE REFERER = 15;
COMMIT;

When I directly input it into the database.

check [mysqli error](http://php.net/manual/en/mysqli.error.php) after each call to catch errors — Iłya Bursov, Feb 23 '18 at 03:04
Possible duplicate of [How to start and end transaction in mysqli?](https://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli) — Iłya Bursov, Feb 23 '18 at 03:05

Zainul Abideen · Accepted Answer · 2018-02-23T04:10:06.000

1

You are supposed to use try catch block to throw the exception. see the example below:

$sql1 = "INSERT INTO ARCHIVE_CLICKS_PATH SELECT * FROM CLICKS_PATH WHERE REFERER = ?;";
$sql2 = "DELETE FROM CLICKS_PATH WHERE REFERER = ?;";
$stmt1 = $conn->prepare($sql1);
$stmt2 = $conn->prepare($sql2);
try{
    $conn->query('BEGIN;');
    if($stmt1 == false || $stmt2 == false || $stmt1->bind_param("i", $b) == false || $stmt2->bind_param("i", $a) == false || $stmt1->execute() == false || $stmt2->execute() == false){
        throw new Exception($conn->error);
    }
    else{
        echo "successful";
    }
    $conn->query('COMMIT;');
}
catch(Exception $e){
    $conn->query("ROLLBACK;");
    echo $e->getMessage();
}

edited Feb 23 '18 at 04:10

answered Feb 23 '18 at 03:14

Zainul Abideen

1,829
15
37

Again it's a syntax issue with mysqli prepared statements specifically, that statement isn't prepared. I'm trying to figure out at what step \ where I'm supposed to execute bind_param etc – Mason Stedman Feb 23 '18 at 03:17
1

are you using PDO? – Zainul Abideen Feb 23 '18 at 03:18
Software I'm updating uses mysqli so I stuck with it for consistency sake. – Mason Stedman Feb 23 '18 at 03:20
I tested that when you first posted it and it works, but it requires running the statements unprepared, which is what I'm trying to avoid. I got it to work a few ways unprepared, but it's preparing it that's causing the issues. – Mason Stedman Feb 23 '18 at 03:33
Much appreciated, I figured out a similar answer within the try catch structure you posted but upvotes for the help as I never woulda identified it as the problem without your post – Mason Stedman Feb 23 '18 at 04:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165692/discussion-between-zainul-abdeen-and-mason-stedman). – Zainul Abideen Feb 23 '18 at 04:14

score 0 · Answer 2 · answered Feb 23 '18 at 04:14

This is what I figured out to solve it based on the answer above in case it ever helps anyone:

try
{
    $conn->autocommit(FALSE);
    $conn->begin_transaction();
    $stmt1=$conn->prepare("INSERT INTO ARCHIVE_CLICKS_PATH SELECT * FROM CLICKS_PATH WHERE REFERER = ?;");
    $stmt1->bind_param('i',$referer);
    if ($stmt1->execute() == false) {
        throw new Exception('Statement 1 Failed');
    }
    $stmt2=$conn->prepare("DELETE FROM CLICKS_PATH WHERE REFERER = ?;");
    $stmt2->bind_param('i', $referer);
    if ($stmt2->execute() == false) {
        throw new Exception('Statement 2 Failed');
    }

    $stmt1->close();
    $stmt2->close();
    $conn->commit();

}
catch(Exception $e)
{
    $conn->rollback();
    throw $e;
}

Transactional statements with mysqli

2 Answers2