MYSQL语法错误与事务？

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?

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