Error inserting a datetime with PDO

Navigation
1

I am changing the entire system from Mysqli to PDO and I am having a problem when trying to insert a date in a field of a record. The record that I try to record is news, which involves a date. The format of the date that I bring with the POST is of type " 2017-07-26 20:52 " and that date and time is automatically taken by the system. While using mysqli I had no problems recording records. 

$conexion = new Conexion();
$stmt = $conexion -> prepare("INSERT INTO noticias (fecha, titulo, resumen, texto, vigente, 
cant_lecturas) VALUES (':fecha', ':titulo', ':sResumen', ':sTexto', ':vigente', 
':cant_lecturas')");
$stmt->bindParam(':fecha', $fecha);
$stmt->bindParam(':titulo', $titulo);
$stmt->bindParam(':sResumen', $sResumen);
$stmt->bindParam(':sTexto', $sTexto);
$stmt->bindParam(':vigente', $vigente);
$stmt->bindParam(':cant_lecturas', $cant_lecturas);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    $resultado = 1;
} else {
    $resultado = null;
}

When executing the insertion process, it throws the following error: 

Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 
1292 Incorrect datetime value: ':fecha' for column 'fecha' at row 1'

What could it be?

    
asked by MNibor 27.07.2017 в 02:14
source

3 answers

4

The error is because when you use prepared queries, the name markers should not be surrounded by quotes:

': date', ': title', ': sResumen', ': sTexto', ': vigente' ...

Regarding the lack of seconds in the chain: 2017-07-26 20:52 I did a test on phpfiddle and PDO adds two zeros when the seconds are missing.

Anyway, if you want, you can build your date from the received string using the class DateTime , although it is not necessary, since the code works without the seconds.

Código: Ver Demo 

<?php
$pdo = new PDO($dsn, $user_name, $pass_word);

/**
 *  Creamos una tabla de prueba 
*/

$sql="CREATE TABLE test_date (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50),
    dt DATETIME
);";
$pdo->query($sql);

/**
 *  Borramos e  insertamos  datos de prueba
 *  ¡Cuidado!  no  uses DELETE  en un  escenario  real
*/

$sql="DELETE FROM test_date";
$pdo->query($sql);

$nombre="Pedro";
$fecha="2017-07-26 20:52";

$sql="INSERT INTO  test_date (nombre,  dt)  VALUES (:nombre, :dt)";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":nombre",$nombre);
$stmt->bindValue(":dt",$fecha);
$stmt->execute();
$sql = "SELECT * FROM test_date";   

$arrDatos = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

if ($arrDatos)
{
    echo "<pre>";
    echo "CONSULTA  1:  SE ENCONTRARON  ".count($arrDatos). " REGISTROS\n";
    print_r($arrDatos);    
    echo "</pre>";


}
else
{
    echo "No hay datos";
}

$pdo = null;

?>

Resultado : 

CONSULTA  1:  SE ENCONTRARON  1 REGISTROS

Array
(
    [0] => Array
        (
            [id] => 9
            [nombre] => Pedro
            [dt] => 2017-07-26 20:52:00
        )

)
    
answered by 27.07.2017 / 06:58
source
1

Apparently you just need to add the seconds: 

$fecha .= ':00';

Then, date will be as: "2017-07-26 20:52:00".

If you still have questions, check the MySQL documentation: link

    
answered by 27.07.2017 в 04:27
0

I found a partial solution and I say partial because it kept any date but recorded the record. It occurred to me to convert to date the variable $ date by doing: 

$fecha = date('Y-m-d H:m', $_POST['fecha']);

The only problem I found is that the date and time value that I took was 

1970-01-01 00:01:00

which is a mystery ... but hey, I managed to record it in the database ...

    
answered by 27.07.2017 в 15:20
How to make the placeholder align to the left Update related tables laravel