[FIXED] Update a single record in database CakePHP

Issue

I am trying to update a single record of the database with the function "updateAll", and filtering by a single "id".

$this->Factura->updateAll(
  array(
    'Factura.I_COBRADO' => $factura['Factura']['I_TOTAL'],
    'Factura.L_COBRADA' => 1,
    'Factura.FH_COBRO' => date('Y-m-d H:i:s'),
    'Factura.S_FORMAPAGO' => 'tarjeta',
    'Factura.FK_FORMAPAGOCOBRO' => 41
  ),
  array(
    'Factura.PK_FACTURA' => $factura['Factura']['PK_FACTURA'])
  )

But I get this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '15:10:55, Factura.S_FORMAPAGO = tarjeta, Factura.FK_FORMAPAGOCOBRO = 41 ' at line 1

SQL Query: UPDATE itramit_daily_desarrollo.facturas AS Factura LEFT JOIN itramit_daily_desarrollo.expedientes AS Expediente ON (Factura.FK_EXPEDIENTE = Expediente.PK_EXPEDIENTE) LEFT JOIN itramit_daily_desarrollo.expedientes AS ExpedienteFactura ON (Factura.FK_EXPEDIENTE = ExpedienteFactura.PK_EXPEDIENTE) LEFT JOIN itramit_daily_desarrollo.municipios AS Municipio ON (Factura.FK_MUNICIPIO = Municipio.PK_MUNICIPIO) LEFT JOIN itramit_daily_desarrollo.valoraciones AS Valoracione ON (Factura.FK_VALORACION = Valoracione.PK_VALORACION) LEFT JOIN itramit_daily_desarrollo.liquidaciones AS Liquidacione ON (Factura.FK_LIQUIDACION = Liquidacione.PK_LIQUIDACION) LEFT JOIN itramit_daily_desarrollo.formaspagoscobros AS Formaspagoscobro ON (Factura.FK_FORMAPAGOCOBRO = Formaspagoscobro.PK_FORMAPAGOCOBRO) SET Factura.I_COBRADO = 165.00, Factura.L_COBRADA = 1, Factura.FH_COBRO = 2021-08-16 15:10:55, Factura.S_FORMAPAGO = tarjeta, Factura.FK_FORMAPAGOCOBRO = 41 WHERE Factura.PK_FACTURA = 166569

Solution

Running your query through MySQL Workbench, I see that

Factura.FH_COBRO = 2021-08-16 15:10:55

is the cause of the syntax error. It appears that CakePHP does not add quotes around the data value.

Try:

'Factura.FH_COBRO' => "'" + date('Y-m-d H:i:s') + "'",
'Factura.S_FORMAPAGO' => "'tarjeta'",

Answered By - Alexander van Oostenrijk