mysqli::rollback

mysqli_rollback

(PHP 5)

mysqli::rollback -- mysqli_rollback — Rolls back current transaction

Description

Object oriented style (method):

bool mysqli::rollback ( void )

Procedural style:

bool mysqli_rollback ( mysqli $link )

Rollbacks the current transaction for the database.

Parameters

link: Procedural style only: A link identifier returned by mysqli_connect() or mysqli_init()

Return Values

Returns TRUE on success or FALSE on failure.

Examples

Example #1 Object oriented style


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* disable autocommit */
$mysqli->autocommit(FALSE);

$mysqli->query("CREATE TABLE myCity LIKE City");
$mysqli->query("ALTER TABLE myCity Type=InnoDB");
$mysqli->query("INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* commit insert */
$mysqli->commit();

/* delete all rows */
$mysqli->query("DELETE FROM myCity");

if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
    $row = $result->fetch_row();
    printf("%d rows in table myCity.\n", $row[0]);
    /* Free result */
    $result->close();
}

/* Rollback */
$mysqli->rollback();

if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
    $row = $result->fetch_row();
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Free result */
    $result->close();
}

/* Drop table myCity */
$mysqli->query("DROP TABLE myCity");

$mysqli->close();
?>

Example #2 Procedural style


<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* disable autocommit */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE myCity LIKE City");
mysqli_query($link, "ALTER TABLE myCity Type=InnoDB");
mysqli_query($link, "INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* commit insert */
mysqli_commit($link);

/* delete all rows */
mysqli_query($link, "DELETE FROM myCity");

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity.\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Rollback */
mysqli_rollback($link);

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Drop table myCity */
mysqli_query($link, "DROP TABLE myCity");

mysqli_close($link);
?>

The above example will output:

0 rows in table myCity.
50 rows in table myCity (after rollback).


Something to consider when using transact is that you should not perform a normal query on the same table (such as a DELETE) immediately after a transaction.  If the transaction rolls-back, the DELETE will execute and even show affected rows, but the row can be magically re-inserted even if the rollback() command comes before the DELETE query.

Lorenzo - webmaster AT 4tour DOT it
10-Feb-2009 02:31


This is an example to explain the powerful of the rollback and commit functions.


Let's suppose you want to be sure that all queries have to be executed without errors before writing data on the database.


Here's the code:





<?php


$all_query_ok=true; // our control variable





//we make 4 inserts, the last one generates an error


//if at least one query returns an error we change our control variable


$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false;


$mysqli->query("INSERT INTO myCity (id) VALUES (200)") ? null : $all_query_ok=false;


$mysqli->query("INSERT INTO myCity (id) VALUES (300)") ? null : $all_query_ok=false;


$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; //duplicated PRIMARY KEY VALUE





//now let's test our control variable


$all_query_ok ? $mysqli->commit() : $mysqli->rollback();





$mysqli->close();


?>





hope to be helpful!

add a note

mysqli::select_db

mysqli::reap_async_query

Last updated: Fri, 06 Nov 2009