PHP: Hypertext Preprocessor (original) (raw)

mysqli::rollback

mysqli_rollback

(PHP 5, PHP 7, PHP 8)

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

Description

Object-oriented style

Return Values

Returns [true](reserved.constants.php#constant.true) on success or [false](reserved.constants.php#constant.false) on failure.

Changelog

Version	Description
8.0.0	name is now nullable.

Notes

Note:

This function does not work with non transactional table types (like MyISAM or ISAM).

Found A Problem?

Steven McCoy ¶

13 years ago

`Remember that MyISAM tables do not support rollbacks.

I just drove myself crazy for an afternoon trying to figure out what was wrong with my code - meanwhile it was fine all along

Lorenzo - webmaster AT 4tour DOT it ¶

16 years ago

`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:

mysqli−>query("INSERTINTOmyCity(id)VALUES(100)")?null:mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : mysqli−>query("INSERTINTOmyCity(id)VALUES(100)")?null:all_query_ok=false; mysqli−>query("INSERTINTOmyCity(id)VALUES(200)")?null:mysqli->query("INSERT INTO myCity (id) VALUES (200)") ? null : mysqli−>query("INSERTINTOmyCity(id)VALUES(200)")?null:all_query_ok=false; mysqli−>query("INSERTINTOmyCity(id)VALUES(300)")?null:mysqli->query("INSERT INTO myCity (id) VALUES (300)") ? null : mysqli−>query("INSERTINTOmyCity(id)VALUES(300)")?null:all_query_ok=false; mysqli−>query("INSERTINTOmyCity(id)VALUES(100)")?null:mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : mysqli−>query("INSERTINTOmyCity(id)VALUES(100)")?null:all_query_ok=false; //duplicated PRIMARY KEY VALUE //now let's test our control variable allqueryok?all_query_ok ? allqueryok?mysqli->commit() : mysqli−>rollback();mysqli->rollback();mysqli−>rollback();mysqli->close(); ?>

hope to be helpful!

Yorick Phoenix ¶

1 year ago

`` If you use savepoints - eg savepoint($foo) - be wary of trying to rollback to the save point with rollback(0, foo)asthatexecutes"ROLLBACK/∗foo) as that executes "ROLLBACK /* foo)asthatexecutes"ROLLBACK/∗foo */" instead of "ROLLBACK TO $foo".

The manual page is clear about this, but is easily overlooked.

Instead use: mysqli−>query("ROLLBACKTO‘mysqli->query("ROLLBACK TO </annotation></semantics></math>mysqli−>query("ROLLBACKTO‘foo");

xcalibur at xcalibur dot dk ¶

15 years ago

`` Just a note about auto incremental ids and rollback.
When using transactions and inserting into a table containing a column with auto incremental ids, the id will be incremented even though the transaction is rolled back.

This might occupy a lot of ids if a lot of rollbacks are performed.

Example:
<?php
$mysqli = new mysqli("localhost", "gugbageri", "gugbageri", "gugbageri");/* check connection /
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}/* disable autocommit */
$mysqli->autocommit(FALSE);/ We just create a test table with one auto incremental primary column and a content column*/
$mysqli->query("CREATE TABLE TestTable ( id_column INT NOT NULL AUTO_INCREMENT , content INT NOT NULL , PRIMARY KEY ( id_column )) ENGINE = InnoDB;");/* commit newly created table /
$mysqli->commit();/ we insert a row /
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");/ we commit the inserted row /
$mysqli->commit();/ we insert another three rows /
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");/ we the rollback /
$mysqli->rollback();/ we insert a row /
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");/ we commit the inserted row */
$mysqli->commit();

if ( result=result = result=mysqli->query("SELECT id_column FROM TestTable")) {

while( row=row = row=result->fetch_row()) {
printf("Id: %d.\n", $row[0]);
}
/* Free result /
$result->close();
}/ Drop table TestTable */ mysqli−>query("DROPTABLETestTable");mysqli->query("DROP TABLE TestTable");mysqli−>query("DROPTABLETestTable");mysqli->close();
?>

This will output:
Id: 1.
Id: 5.