How to rollback multi table updates

[Waspman]

Not sure if this is the correct terminology, but I have 3 separate transactions to complete Below is skeleton of the code, and it works ok

$ok = TRUE;
$sql = "INSERT INTO `members`...";
$stmt = $conn->prepare($sql);
if (
        $stmt &&
        $stmt->bind_param(...) &&
        $stmt->execute()) {
    $memid = $stmt->insert_id;
    mysqli_stmt_close($stmt);
    $sql = "UPDATE `waitlist`...";
    $stmt = $conn->prepare($sql);
    if (
            $stmt &&
            $stmt->bind_param(...) &&
            $stmt->execute()) {
        mysqli_stmt_close($stmt);
        $sql = "INSERT INTO `classmembers`...)";
        if ($conn->query($sql)) {
            $ok = TRUE;
        } else {
            die(mysqli_error($conn));
        }
    } else {
        die(htmlspecialchars($conn->error));
    }
} else {
    die(htmlspecialchars($conn->error));
}
if ($ok) {
    //further actions
    header('location: home.php');
} else {
    // INSERT INTO `classmembers will not have happened so can be ignored
    // Delete from `members` is easy to do
    // UPDATE `waitlist` is more problematic because there is a comments field that has been updated.
}

However is there a better way of rolling back the transactions if any fail other than the way indicated in the final “else” block?

[astonecipher]

The way this is done is to use transactions.

turn off Auto commit
Start the transaction.
try {
    run queries

   last line, commit the transaction
} catch (Exception $e) {
    if there are database exceptions throw, roll back the transaction
}

[Waspman]

Thanks for reply - have just Googled transactions and have come across something that says not all db engines support this.

In the database in use two of the tables are InnoDB (as some of the columns are AES_encoded), the other ise MyISAM.

Is this ok

[astonecipher]

MyISAM does not, but I have never found a reason to not use InnoDB or Memory engines. MyISAM also does not support foreign keys, and therefore is a poor choice to use in a relational database scheme.

[Waspman]

Thanks for that - I’ll investigate the database and see what there is in it, and convert them all to InnoDB.