Edit Report a Bug

mysqli::commit

mysqli_commit

(PHP 5, PHP 7, PHP 8)

mysqli::commit -- mysqli_commit — Consigna la transacción actual

Descripción

Estilo orientado a objetos

mysqli::commit(int $flags = ?, string $name = ?): bool

Estilo por procedimientos

mysqli_commit(mysqli $link, int $flags = ?, string $name = ?): bool

Consigna la transacción actual para la conexión a la base de datos.

Parámetros

link: Sólo estilo por procediminetos: Un identificador de enlace devuelto por mysqli_connect() o mysqli_init()
flags: Una máscara de bits de constantes MYSQLI_TRANS_COR_*.
name: Si se proporciona, se ejecuta COMMIT/*name*/.

Valores devueltos

Devuelve true en caso de éxito o false en caso de error.

Historial de cambios

Versión	Descripción
5.5.0	Se añadieron los parámetros `flags` y `name`.

Ejemplos

Ejemplo #1 Ejemplo de mysqli::commit()

Estilo orientado a objetos

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

/* Comprobar la conexión */
if (mysqli_connect_errno()) {
    printf("Falló la conexión: %s\n", mysqli_connect_error());
    exit();
}

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

/* Desactivar la autoconsigna */
$mysqli->autocommit(FALSE);

/* Inserta algunos valores */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* Consignar la transacción */
if (!$mysqli->commit()) {
    print("Falló la consignación de la transacción\n");
    exit();
}

/* Eliminar la tabla */
$mysqli->query("DROP TABLE Language");

/* Cerrar la conexión */
$mysqli->close();
?>

Estilo por procedimientos

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

/* Comprobar la conexión */
if (!$enlace) {
    printf("Falló la conexión: %s\n", mysqli_connect_error());
    exit();
}

/* Desactivar la autoconsignación */
mysqli_autocommit($enlace, FALSE);

mysqli_query($enlace, "CREATE TABLE Language LIKE CountryLanguage");

/* Insertar algunos valores */
mysqli_query($enlace, "INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
mysqli_query($enlace, "INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* Consignar la transación */
if (!mysqli_commit($enlace)) {
    print("Falló la consignación de la transacción\n");
    exit();
}

/* Cerrar la conexión */
mysqli_close($enlace);
?>

Ver también

mysqli_autocommit() - Activa o desactiva las modificaciones de la base de datos autoconsignadas
mysqli_begin_transaction() - Inicia una transacción
mysqli_rollback() - Revierte la transacción actual
mysqli_savepoint() - Define el nombre de un punto de salvaguarda de la transacción

add a note

User Contributed Notes 6 notes

down

snchzantonio at gmail dot com ¶

11 years ago


I never recomend to use the ? with only one value variant like: $var = expression ? $var  : other_value or $var = expression ? null  : other_value ,and php suport Exception catchin so,use it :)

here my opinion abut lorenzo's post:

  <?php
 
//variants combined

$mysqli->autocommit(FALSE);

try{

  $mysqli->query("INSERT INTO myCity (id) VALUES (100)") or throw new Exception('error!');

// or we can use

  if( !$mysqli->query("INSERT INTO myCity (id) VALUES (200)"){ 
    throw new Exception('error!'); 
  }

}catch( Exception $e ){
  $mysqli->rollback();
}
$mysqli->commit();

?>

down

mvanlamz ¶

15 years ago


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.

down

Lorenzo - webmaster AT 4tour DOT it ¶

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

<?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!

down

NoMan2000 ¶

9 years ago


This is to clarify the Flags parameters and what they mean:

MYSQLI_TRANS_COR_AND_CHAIN: 

Appends "AND CHAIN" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_AND_NO_CHAIN: 

Appends "AND NO CHAIN" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_RELEASE: 

Appends "RELEASE" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_NO_RELEASE:

Appends "NO RELEASE" to mysqli_commit or mysqli_rollback.

To clarify those options:

The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. 

The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction.

down

zattechnology at gmail dot com ¶

5 years ago


When you have alot of transactions to make, say you are applying inserting items to the database from a loop, it will be better to use the mysqli_commit for this kind of process as it will only hit the database once.

//Wrong way
Example 1:

$con = mysqli_connect("host", "username", "password", "database") or die("Could not establish connection to database");

$users = ["chris", "james", "peter", "mark", "joe", "alice", "bob"]

for($i=0; $i<count($users); $i++){
       $user= $users[$i];
       $query = mysqli_query($con, "INSERT INTO users (username) VALUES ('$user') ");
} 

//Correct Way
Example 2

$con = mysqli_connect("host", "username", "password", "database") or die("Could not establish connection to database");

$users = ["chris", "james", "peter", "mark", "joe", "alice", "bob"]

//Turn off autocommit 
mysqli_autocommit($con, FALSE)

//Make some transactions
for($i=0; $i<count($users); $i++){
     $user= $users[$i];
     $query = mysqli_query($con, "INSERT INTO users (username) VALUES ('$user') ");
} 

//Make a one-time hit to the database 
mysqli_commit($con)

As with the Example 1, since we had 7 items in the list, this means that their will be a 7 times hit to our database which can really affect performance. But with the Example 2, since we already turned off autocommit this means that the transactions will be queued ontill will explicitly call mysqli_commit($con)

down

-15

Bob Johnson ¶

15 years ago


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

                }

?>

add a note