MYSQL syntax error with transactions?

Question

I'm having this error when I try to use transactions:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO pagos (userID, pagoMonto, pagoFecha, pagoMedioUtilizado, pagoCuentaO' at line 2

This is the query that I'm trying:

START TRANSACTION;
INSERT INTO pagos (userID, pagoMonto, pagoFecha, pagoMedioUtilizado, pagoCuentaOrigen, pagoSucursal, pagoCodigo) 
VALUES('$userID', '$pagoMonto', '$pagoFecha', '$pagoMedioUtilizado', '$pagoCuentaOrigen', '$pagoSucursal', '$pagoCodigo');
INSERT INTO pagosVerificados (pagoID, userID, cursoID) 
VALUES(LAST_INSERT_ID(), '$userID', '$cursoID');
COMMIT;

I've got all my tables as InnoDB.

MySQL version: 5.6.30

I'm testing to see if there's an issue with the variables content, so I've printed them out:

UserID: 16
cursoID: 15
pagoMonto: 25
pagoFecha: 2016-05-01
pagoMedioUtilizado: efectivo
pagoCuentaOrigen: 216852
pagoSucursal: 55
pagoCodigo: 55555

I'm using the syntax read at the manual for the 5.6 version. Where is the error?

Did you scrub your variables to make sure there are no single quotes? — Jason K
– Jason K, Commented Jul 28, 2016 at 18:52
I haven't worked with transactions in MySQL that much but, are you sure that should be run as one "query"? (Actually, I just noticed the two inserts; I am fairly certain you cannot run those as one query.) — Uueerdo
– Uueerdo, Commented Jul 28, 2016 at 19:44

Bill Karwin · Accepted Answer · 2016-07-28 18:59:09Z

1

I believe you are trying to run multiple statements in one call. Don't do that. Run one statement per call.

Use PDO methods for transactions, and use prepared queries instead of interpolating PHP variables into your SQL strings:

$pdo->beginTransaction();

$stmt = $pdo->prepare("INSERT INTO pagos 
  SET userID=?, pagoMonto=?, pagoFecha=?, pagoMedioUtilizado=?,
      pagoCuentaOrigen=?, pagoSucursal=?, pagoCodigo=?");
$stmt->execute([$userID, $pagoMonto, $pagoFecha, $pagoMedioUtilizado,
  $pagoCuentaOrigen, $pagoSucursal, $pagoCodigo]);

$stmt = $pdo->prepare("INSERT INTO pagosVerificados 
  SET pagoID=LAST_INSERT_ID(), userID=?, cursoID=?");
$stmt->execute([$userID, $cursoID]);

$pdo->commit();

answered Jul 28, 2016 at 18:59

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL syntax error with transactions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related