Rosamunda Rosamunda - 1 year ago 91
PHP Question

MYSQL syntax error with transactions?

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:

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');

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 Source

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:


$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]);

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download