CODEWITHSUNDEEP
phpmysqltransactions
JorgeeFG
JorgeeFG

Reputation: 5981

mySQL Transaction not working

I have the following code:

<?php

// Conexion a MySQL



$mysql_link = mysql_connect( 'localhost', 'root', '010101' );

if  (!$mysql_link) {
    die('No se pudo conectar a la DB: ' . mysql_error());

}

$mysql_db = mysql_select_db('test', $mysql_link);

if (!$mysql_db) {
die ('No se pudo seleccionar DB: ' . mysql_error());
    }

$mysql_doc_query = "INSERT INTO documents (name, wfid, docid, archivo) VALUES     ('{$CodDoc}: {$documentoNombre} de {$DNI}', '{$workflowNombre}', '{$documentoNombre}',     '{$archivoNombre}' );
      INSERT INTO keywords (document_id, keyword, value) VALUES (LAST_INSERT_ID(),     'DNI', '{$DNI}' ), (LAST_INSERT_ID(), 'Cuit Empleador',
      '{$cuitEmpleador}' ), (LAST_INSERT_ID(), 'DigitalizadoPor', '{$usuario}' ),
  (LAST_INSERT_ID(), 'Direccion IP', '{$IP}' ), (LAST_INSERT_ID(), 'Ubicacion',     CONCAT('pdfs/',LAST_INSERT_ID(),'.pdf') );";

   // Insert en mysql
 $log = fopen('/dev/shm/log.txt', 'w');
  if( $log ) {
      fwrite( $log, $mysql_doc_query );
  }

  mysql_query("START TRANSACTION");
  if (mysql_query($mysql_doc_query) == TRUE)
  {
      mysql_query("COMMIT");
  echo "\nCOMMIT!";
  }
    else {
      mysql_query("ROLLBACK");
      echo "\nROLLBACK!";
  }
  mysql_close($mysql_link);
fclose ($log);
?>

It's always giving me ROLLBACK but I don't understand why.

Any clue on this? The code generated in the log.txt archive can be executed in PHP MY ADMIN without problems. (I know the variables aren't referenced but this is part of a larger script).

Thanks a lot.

Upvotes: 1

Views: 1500

Answers (4)

Razvan
Razvan

Reputation: 10101

The standard way to run transactions consists in the following steps: 

 mysql_query("SET AUTOCOMMIT=0"); //by default this is 1
 mysql_query("START TRANSACTION"); // start the transaction

 //run all the queries, possibly in a loop
 $q1 = mysql_query("INSERT INTO  .... ");
 $q2 = mysql_query("INSERT INTO  .... ");

 //check the success of all the queries and based on that commit or rollback
 if ($q1 and $q2) {
     mysql_query("COMMIT");
 } else {        
     mysql_query("ROLLBACK");
 }

Upvotes: 1

NotGaeL
NotGaeL

Reputation: 8504

You can't send two mysql queries at the same time. You're sending two different INSERT queries.

Also you must send your queries without ';' since it's added automatically.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

mysql_query() only supports one statement at a time. You are executing multiple INSERT statements in one go:

$mysql_doc_query = "INSERT INTO documents (name, wfid, docid, archivo) VALUES     ('{$CodDoc}: {$documentoNombre} de {$DNI}', '{$workflowNombre}', '{$documentoNombre}',     '{$archivoNombre}' );
      // Oops, new statement here! mysql_query() can't do that.
      INSERT INTO keywords (document_id, keyword, value) VALUES (LAST_INSERT_ID(),     'DNI', '{$DNI}' ), (LAST_INSERT_ID(), 'Cuit Empleador',
      '{$cuitEmpleador}' ), (LAST_INSERT_ID(), 'DigitalizadoPor', '{$usuario}' ),
  (LAST_INSERT_ID(), 'Direccion IP', '{$IP}' ), (LAST_INSERT_ID(), 'Ubicacion',     CONCAT('pdfs/',LAST_INSERT_ID(),'.pdf') );";

This could be debugged with mysql_error() inside your ROLLBACK block:

  if (mysql_query($mysql_doc_query) == TRUE)
  {
      mysql_query("COMMIT");
  echo "\nCOMMIT!";
  }
  else {
    echo "Error in query: " . mysql_error();
    mysql_query("ROLLBACK");
    echo "\nROLLBACK!";
  }

If you need to do two INSERTs, you will need two separate calls to mysql_query(), and check for errors after each one. On failure of either, do your ROLLBACK.

Upvotes: 1

Marc B
Marc B

Reputation: 360872

Your testing method is bad. mysql_query() returns a boolean FALSE on error, or a result hand on success. By PHP's type conversion rules, a result handle tests equal to true. You must use the strict boolean comparison, and excplicitly test against false:

if (mysql_query($mysql_doc_query) !== FALSE)

Upvotes: 1

Related Questions