mysqli::commit

mysqli_commit

(PHP 5)

mysqli::commit -- mysqli_commit — Commits the current transaction

Description

Object oriented style (method)

bool mysqli::commit ( void )

Procedural style:

bool mysqli_commit ( mysqli $link )

Commits the current transaction for the database connection.

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();
}

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage Type=InnoDB");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

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

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

Example #2 Procedural style


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

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

/* set autocommit to off */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE Language LIKE CountryLanguage Type=InnoDB");

/* Insert some values */
mysqli_query($link, "INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
mysqli_query($link, "INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

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

/* close connection */
mysqli_close($link);
?>


The compactness of Lorenzo's code is admirable.


However, it is a good idea to also check  $mysqli->affected_rows to make sure that the INSERT statement did not fail. 





<?php


$result_query = @mysqli_query($query, $connect);


                if (($result_query == false) &&


                   (mysqli_affected_rows($connect) == 0))


                 {


                    // verify the query executed completely and verify that it


                    // had impact on the table





                    $success = false;





                    // here also, the developer could choose to add a ROLLBACK


                    // statement


                }


?>

mvanlamz
31-Mar-2009 09:36


Please note that calling mysqli::commit() will NOT automatically set mysqli::autocommit() back to 'true'.



This means that any queries following mysqli::commit() will be rolled back when your script exits.

Lorenzo - webmaster AT 4tour DOT it
11-Feb-2009 12:12


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->connect_errno

mysqli::close

Last updated: Fri, 20 Nov 2009