Tags
Asked 3 years ago
9 Jun 2021
Views 215
jagdish

jagdish posted

transaction query over normal php mysqli query in Mysqli

transaction query over normal php mysqli query in Mysqli
QuickIos

QuickIos
answered Apr 26 '23 00:00

Using transactions in MySQLi can be useful in situations where you need to execute multiple queries as a single unit of work, ensuring that either all the queries complete successfully, or none of them do.

Here's an example of how to use transactions with MySQLi in PHP:


<?php
// Create a new MySQLi instance
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check for connection errors
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

// Start a transaction
$mysqli->begin_transaction();

// Execute the first query
$query1 = "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
$result1 = $mysqli->query($query1);

// Check for errors
if (!$result1) {
    // Rollback the transaction and exit
    $mysqli->rollback();
    echo "Failed to execute query: " . $mysqli->error;
    exit();
}

// Execute the second query
$query2 = "UPDATE accounts SET balance = balance + 100 WHERE id = 2";
$result2 = $mysqli->query($query2);

// Check for errors
if (!$result2) {
    // Rollback the transaction and exit
    $mysqli->rollback();
    echo "Failed to execute query: " . $mysqli->error;
    exit();
}

// Commit the transaction
$mysqli->commit();

// Free up resources
$mysqli->close();
?>

In this example, we start by creating a new MySQLi instance and checking for connection errors. We then start a transaction using the begin_transaction() method. We execute the first query inside the transaction, checking for errors and rolling back the transaction if necessary. We then execute the second query, again checking for errors and rolling back the transaction if necessary. Finally, we commit the transaction using the commit () method.

Using transactions in MySQLi can help ensure that your database stays in a consistent state, even if errors occur during the execution of multiple queries.
Post Answer