PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is Transaction in mysql?
In SQL, successful transaction means that all SQL statements has been executed successfully. If any error occurs, then the data should be a rollback to avoid data inconsistency. The transaction will not complete unless all operations of that transaction successfully completes, if any, of the operation fails it mean complete transaction fails.
Example :- Money transfer
File Name :
Check the balance of the transferred account to see if the amount is sufficient for the transfer.
If the amount is sufficient, deduct the amount from the balance of the transferred account.
Add the transfer amount to the balance of the receiving account.
If an error occurs in the second step, the third step should not continue. In addition, if an error occurs in the third step, the second step must be reversed. The amounts of both bank accounts are intact in case of failure or adjusted correctly if the transaction is completed successfully.
If you are doing a money transfer transaction from one bank account to another, at that time, if there is an interruption due to the Internet/Server or some other problem, then the transaction will be rolled back to its original stage and your money will be refunded to your account.
Handle Transaction
File Name :
To handle MySQL transaction in PHP, you use the following steps:
Start the transaction by calling the beginTransaction() method of the PDO object.
Place the SQL statements and the commit() method call in a try block.
Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.
Example :
File Name :
<?php
//$id = 1;
//connect to mysql database
$conn = mysqli_connect('localhost', 'root', '', 'test');
mysqli_autocommit($conn, false);
$state = true;
$req1 = "INSERT INTO `customers` (`id`, `name`, `age`) VALUES ($id, 'Sana', 2)";
$req2 = "INSERT INTO `Orders` (`id`, `num_ord`, `c_id`) VALUES (1, '786', $id)";
$res = mysqli_query($conn, $req1);
if (!$res) {
$state = false;
echo "Error: " . mysqli_error($conn) . ".";
}
$res = mysqli_query($conn, $req2);
if (!$res) {
$state = false;
echo "Error: " . mysqli_error($conn) . ".";
}
if ($state == true) {
mysqli_commit($conn);
echo "Transaction successfully Commited";
} else {
mysqli_rollback($conn);
echo "Transaction Rollbacked.";
}
mysqli_close($conn);
?>
When you run the function mysqli_query(), the result is immediately validated in the database. By using the mysqli_autocommit() function, you can disable this behavior so that the result is not permanently validated in the database until you validate it. At the end, if the variable “state” contains true (So, no errors occurred), we validate the results in the database permanently using mysqli_commit(). Otherwise, we roll back the results using mysqli_rollback().
Example
File Name :
<?php
$conn = mysqli_connect('localhost', 'root', '', 'test');
/* Turn autocommit off */
// $mysqli->autocommit(false);
$conn->begin_transaction();
$req1 = "INSERT INTO `customers` (`id`, `name`, `age`) VALUES (1, 'sana', 2)";
$req2 = "INSERT INTO `Orders` (`id`, `num_ord`, `c_id`) VALUES (1, '454', 7)";
$res1 = mysqli_query($conn, $req1);
$res2 = mysqli_query($conn, $req2);
if( !empty($res1) && !empty($res2) )
{
$conn->commit();
echo "success";
}
else{
echo "All queries have been canceled";
$conn->rollback();
}
mysqli_close($conn);
?>
Transaction in Codeigniter
File Name :
$this->db->trans_start();
$result1 = "insert query";
$result2 = "update query";
$this->db->trans_complete();
//check if transaction status TRUE or FALSE
if ($this->db->trans_status() === FALSE) {
//if something went wrong, rollback everything
$this->db->trans_rollback();
}
else
{
//if everything went right
$this->db->trans_commit();
}
Transaction
File Name :
$sql=mysqli_connect("localhost","root","root","ittutorial") or die(mysqli_error());
// transaction start
// $mysqli->autocommit(false);
$sql->begin_transaction();
$result1 = mysqli_query($sql,"UPDATE ittutorial SET name='mahtab' WHERE id='".$id."'");
$result2 = mysqli_query($sql,"UPDATE itechxpert SET title='".$title."' WHERE fki_id='".$id."'");
$result3 = mysqli_query($sql, "INSERT INTO employee SET name='mahtab',address='delhi'");
if(!empty($result1) && !empty($result2) && !empty($result3))
{
$sql->commit();
}
else{
$sql->rollback();
}