Monday, 11/12/2017 | 9:39 UTC+5
Mehta Websolution's Blog

MySQL Transactions

MySQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state.

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

Think you need to add data in 2 tables

Here you can see in second query gender field having speeling mistake, hence no data will be added for this operation and data from first query will be inserted successfully.When running these two statements, think that second one fails and first one succeeds.

If we ran these two statements in a MySQL transaction, if the second statement fails then the first statement will be rolled back making no changes.

In PHP, we can execute a transaction using MySQLi functions like below.

When you execute mysqli_query() function, result is immediately committed to the database. Using mysqli_autocommit() function, you can turn off this behavior so that result won’t be committed to the database permanently till you command.

After that we simply execute the necessary statements and set the $flag to false if any statement fails. If there are many statements to run, consider storing the statements in an array and using a for or foreach loop.

At the end, if flag is true (no error has occurred), we commit the results to the database permanently using mysqli_commit(). Else we roll back the results using mysqli_rollback() function.

If you ran above PHP script, you would notice that no statement is executed since the field name gender having spelling mistake.While transactions will help you to eliminate data corruptions in programming level.

About

Mehta Websolution is a web development company based in Jamnagar, Gujarat, India. providing a full range of web services including web design, web development, web promotion, search engine optimization, web hosting and domain registration and all Internet services at accessible cost since it was established in 2007. A company which provides you a full range of web services Contact us for web design, web development, web promotion, search engine optimization, web hosting and domain registration at an affordable cost. IF you are looking for an IT Solutions Provider who can implement your Dreams in a reality form of exhaustive web applications, innovative software, and creative designs or for fastidious solutions and persistent services, you are at the right place. we provide you software development and web solutions all across the globe. Our strategic location and proven experience enables us to provide you with the best services and outsourcing for your Software Development, Web Development, E-commerce, Multimedia Presentations and Web Based Applications at the corporate level.

POST YOUR COMMENTS

Your email address will not be published. Required fields are marked *

18 − twelve =

Mehta Websolution Since 2007

Company is Government & Defence (Indian Navy, Army & Air Force) Approval I.T. Contractor, An ISO 9001:2008 Certified I.T. Company and Google Enterprise for Work Partner.