Dependent insertion of data into MySql table

Question

I have 2 tables:

user_tb.username user_tb.point

review_tb.username review_tb.review

I am coding with PHP(CodeIgniter). So I am trying to insert data into review_tb with the review the user had submitted and if that is a success, i will award the user with some points.

Well this look like a very simple process. We will first insert the review into the review_tb with the username and use PHP to check if there is any problem with the query executed and if it's a success, we will proceed with updating the points in the user_tb.

Yea, but here comes the problem. What if inserting into review_tb is a success but the second query, inserting into the user_tb is NOT a success, can we kind of "undo" the review_tb query or "revert" the change that we did to review_tb.

It's kind of like "all or nothing".

The purpose of this is to sync all data across the database, where in real life, we will be managing a database of more tables, and inserting more data into each table which depends on each other.

Please give some enlightenment on how we can do this in PHP or CodeIgniter or just MySql query.

Answer 1

Can't you use transactions? If you did both inserts inside the same transaction, then either both succeed or neither does.

Try something like

BEGIN;
INSERT INTO review_tb(username, review) VALUES(x, y);
INSERT INTO user_tb(username, point) VALUES(x, y);
COMMIT;

Note that you need to use a database engine that supports transactions (such as InnoDB).

Answer 2

Hi,

If you want a "all or nothing" behavior for your SQL operations, you are looking for transactions ; here is the relevant page from the MySQL manual : 12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax.

Wikipedia describes those this way :

A database transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. Transactions in a database environment have two main purposes:

1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
2. To provide isolation between programs accessing a database concurrently. Without isolation the programs' outcomes are typically erroneous.

Basically :

• you start a transaction
• you do what you have to ; ie, your first insert, and your update
• if everything is OK, you commit the transaction
• else, if there is any problem with any of your queries, you rollback the transaction ; and it will cancel everything you did in that transaction.

There is a manual page about transactions and CodeIgniter here.

Note that, with MySQL, no every Engine supports transaction ; between the two most used engines, MyISAM doesn't support transactions, while InnoDB supports them.

Answer 3

If you have InnoDB support use it, but when its not possible you can use a code similar to the following:

 $result=mysql_query("INSERT INTO ...");
 if(!$result) return false;

 $result=mysql_query("INSERT INTO somewhereelse");
 if(!$result) {
      mysql_query("DELETE FROM ...");
      return false;
 }

 return true;

This cleanup might still fail, but can work whenever the insert query fails because of duplicates or constraints. For unexpected terminations, only way is to use transactions.