Use the new PHP plug-in to implement MySQL-based transactions

Use the new PHP plug-in to implement MySQL-based transactions. read about using the new PHP plug-in to implement MySQL-based transactions. transaction processing support has been the wish of most MySQL developers for a long time, with the release of MySQL4.0, this wish was finally realized. Shortly after MySQL4.0, PHP5.x with a new MySQL plug-in was also released. This new plug-in "> <LINKhref =

Transaction processing support has been the wish of most MySQL developers for a long time. with the release of MySQL 4.0, this wish was finally realized. Shortly after MySQL 4.0, PHP 5.x with a new MySQL plug-in was also released. This new plug-in, MySQL Improved, enables PHP developers to use local PHP functions to obtain these new transaction processing capabilities. This short tutorial will show you how to use these new MySQLi functions to implement MySQL-based transactions using PHP.

Summary

If you do not know, I can tell you that transactions are just a set of SQL statements, usually because they are mutually dependent, therefore, it must be executed in all-or-nothing mode. A transaction is successful only when all the statements are successfully executed. failure in any statement should cause the system to roll back to its previous state, to avoid data connection/crash issues.

The transfer between two bank accounts is a good example. At the database level, such transfer involves two steps: first, deduct the transfer amount from the source account and add it to the target account. If an error occurs in step 2, the first step must be canceled to avoid inconsistencies (with angry customers ). The transaction security system automatically removes the snapshot from the system ".

Most databases (including MySQL) use a combination of commands to accomplish this:

* The start transaction command marks the START of a new TRANSACTION group. It is followed by a series of SQL commands.

* The COMMIT command indicates the end of a transaction group, indicating that all changes made during the transaction should be committed or made permanent.

* The ROLLBACK command indicates the end of a transaction group, indicating that all changes made during the transaction should be undone.

Transaction processing functions in PHP

The MySQLi plug-in PHP introduces new functions to help developers use MySQL's transaction processing capabilities. Essentially, these functions are called SQL START TRANSACTION, COMMIT, and ROLLBACK commands. List A shows an example. List:

　　

// Connect to database

$ Dbh = mysqli_connect ($ host, $ user, $ pass, $ db );

// Turn off auto-commit

Mysqli_autocommit ($ dbh, FALSE );

// Run query 1

$ Result = mysqli_query ($ dbh, $ query1 );

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// Run query 2

$ Result = mysqli_query ($ dbh, $ query2 );

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// And so on...

// Assuming no errors, commit transaction

Mysqli_commit ($ dbh );

// Close connection

Mysqli_close ($ dbh );

?>
 

There are three basic steps for executing a transaction in PHP:

* The first step is to always turn off the "auto-commit" of the database, which essentially means that the system will save them when you make changes. This is very important, because in a transaction processing environment, you should be only after you have determined that all the transaction processing units have been completed successfully, save your changes. You can use the mysqli_autocommit () function to disable automatic database submission.

* Next, use the mysqli_query () function to continue to perform INSERT, UPDATE, and/or DELETE queries using the common method. It is important to check the value returned by each query to determine whether the query is successful. If any query fails, the mysqli_rollback () function is used to return the system to the status before the transaction is executed.

* If all the commands in the transaction group are successfully executed, you must use the mysqli_commit () function to save the changes to the database system. Please note that once this function is called, the transaction cannot be undone.

Work instance

To learn how this works in practice, let's go back to the bank transfer example discussed earlier. assume that your task is to create a simple Web application that allows users to transfer funds between their bank accounts. Let's further assume that the account of a single user is stored in a MySQL database, as shown below:

Mysql> SELECT * FROM accounts;

+ ---- + ------------ + --------- +

| Id | label | balance |

+ ---- + ------------ + --------- +

| 1 | Savings #1 | 1000 |

| 2 | Current #1 | 2000 |

| 3 | Current #2 | 3000 |

+ ---- + ------------ + --------- +

3 rows in set (0.34 sec)
 

Now, you need to create a simple interface so that you can enter a cash amount to transfer money from one account to another. The actual "transaction" is executed using two UPDATE statements. one is to take the transfer amount from the source account, that is, the borrower, and the other is to log the transfer amount to the target account, that is, the lender. Assume that we are transferring funds between accounts, the total balance available for all accounts ($6000) should remain unchanged.

List B shows possible code. List B:

　　

// Connect to database

$ Dbh = mysqli_connect ("localhost", "user", "pass", "test ")
Or die ("Cannot connect ");

// Turn off auto-commit

Mysqli_autocommit ($ dbh, FALSE );

// Look for a transfer

If ($ _ POST ['submit '] & is_numeric ($ _ POST ['amt']) {

// Add $ to target account

$ Result = mysqli_query ($ dbh, "UPDATE accounts SET
Balance = balance + ". $ _ POST ['amt ']." WHERE id = ". $ _ POST ['to']);

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// Subtract $ from source account

$ Result = mysqli_query ($ dbh, "UPDATE accounts
SET balance = balance-". $ _ POST ['amt '].
"WHERE id =". $ _ POST ['from']);

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// Assuming no errors, commit transaction

Mysqli_commit ($ dbh );

}

// Get account balances

// Save in array, use to generate form

$ Result = mysqli_query ($ dbh, "SELECT * FROM accounts ");

While ($ row = mysqli_fetch_assoc ($ result )){

$ Accounts [] = $ row;

}

// Close connection

Mysqli_close ($ dbh );

?>
 
As you can see, the script starts by connecting to the database and disabling automatic submission. Then, execute a SELECT query to retrieve the cash receipts and payments of all accounts. then, construct a drop-down table and SELECT the source/target account to be used for the transaction. Chart A shows the original table.

Initial table

After the table is submitted, the two UPDATE queries start to actually perform the borrow and loan operations. Note that the end of each query has a mysqli_rollback (). if the query fails, it will be activated. If no query fails, the new table is saved to the database by calling mysqli_commit. Then the database connection is closed.

You can try it by yourself, from Savings #1 to $500 to Current #2. Once you transfer funds, you will see the new results of the balance table shown in Table B.

Status after the transaction is completed.

Note: Of course, this is just a simple dual-command transaction. Generally, when many SQL statements need to be executed together, you can use this transaction model. The failure of a statement affects the concatenation of other statements. In these cases, you will find that calling mysqli_query () and mysqli_rollback () is compressed into a separate user-defined function, which is easier to call when needed.

As you can see, using PHP and MySQL to execute a transaction processing model can make your MySQL database more stable for query execution errors. However, before you begin to rewrite the code and use this model, it is worth noting that transactions actually increase the consumption of system performance management. Therefore, before you implement this model, it is always a good idea to make a cost-benefit analysis.