'Incorrect datetime value[...]' when using bindParam or bindValue

Asked

Viewed 102 times

-1

I am trying to enter a register in the database as follows (PHP)

    $sql = "INSERT INTO `tb_teste` (`estrutura`, `foto`, `data_cadastro`) VALUES (':estrutura', ':foto', ':data_cadastro')";

    $sql = $this->db->prepare($sql);

    $sql->bindParam(":estrutura", $estrutura, PDO::PARAM_STR);
    $sql->bindParam(":foto", $foto, PDO::PARAM_STR);
    $sql->bindParam(":data_cadastro", $data_cadastro, PDO::PARAM_STR);

    $retorno = $sql->execute();

I’ve tried with bindValue, I have tried without using the last parameter PDO::PARAM_STR, already tried to convert to Timestamp, converted before sending to PHP (in jQuery), converted several different ways in PHP and nothing worked, I keep getting the error:

Incorrect datetime value: ':data_cadastro' for column 'data_cadastro' at Row 1

The only thing I did and inserted into the bank was this:

$sql = "INSERT INTO `tb_teste` (`estrutura`, `foto`, `data_cadastro`) VALUES (':estrutura', ':foto', '$data_cadastro')";

$sql = $this->db->prepare($sql);

$sql->bindParam(":estrutura", $estrutura, PDO::PARAM_STR);
$sql->bindParam(":foto", $foto, PDO::PARAM_STR);

putting the date variable in the query did not give error and entered the date in the field correctly, but the other fields were with the value :estutura,:foto

NOTE: value displayed in var_dump($data_registration):

C: wamp64 www TEST controllers panel testeController.php:57:string '1995-11-14 00:00:00'

That is, when I try to use bindParam or bindValue the insertion does not work, but if the value is inserted in the query, it works correctly.

Thank you!

php sql binding

  • can display the variable value data_cadastro? seems to me to be a date format problem

  • @Ricardopunctual The value of the variable is '1995-11-14 00:00:00', updated there in the post also

  • vc converts the format date dd/mm/YYYY for YYYY-mm-dd ? looks like there’s an error in the conversion.

  • @rray I was able to insert the value of the $data_cadastre variable into the database, but only by placing the variable directly in the sql query, as in the second example... the error only occurs when it passes through the bindParam

  • humm is not missing prepare this query? something like $sql = $mysqli->prepare("INSERT INTO .....?

  • @Ricardopunctual I’m already doing it, I’ll update there including

  • In the first example there are simple quotes left, ':data_cadastro'), no quotation marks on the place.

  • Why the ":"? $sql->bindParam("estrutura", $estrutura, PDO::PARAM_STR);
$sql->bindParam("foto", $foto, PDO::PARAM_STR);

  • @Maurydeveloper followed the PHP documentation https://www.php.net/manual/en/pdostatement.bindparam.php

  • Actually I said the quotes are useless, :.

Show 5 more comments

1 answer

3


Take a look at the code below:

$sql = $this->db->prepare("INSERT INTO tb_teste (estrutura, foto, data_cadastro) VALUES (:estrutura, :foto, :data_cadastro)");    
$sql->bindValue(':estrutura', $estrutura);
$sql->bindValue(':foto', $foto);
$sql->bindValue(':data_cadastro', $data_cadastro);
$retorno = $sql->execute();

Note that I changed the method bindParam for bindValue, but why?

The method bindParam uses the reference variable and not its value as the method bindValue proposes. Example:

$estrutura = 'Alicerce';
$sql = $this->db->prepare("INSERT INTO tb_teste (estrutura) VALUES (:estrutura)");    
$sql->bindParam(':estrutura', $estrutura);
$estrutura = 'Foo Bar';
$retorno = $sql->execute(); // INSERT INTO tb_teste (estrutura) VALUES ('Foo Bar')"

In your case, using both methods does not interfere with the result but I explained this so that you understand the difference between them.

And another caveat is the method prepare. You must use it to prepare the SQL statement with the defined parameters that must be executed by the method execute. Thus, the placeholders inserted in the string will be replaced by the values of the variables after the validations made by PDO.

  • 1

    Thanks man, it worked!!! I’m all morning getting beat up for this and just take the quotes out

  • 1

    Victor just "try this way" is not a good explanation for an answer, can detail what "this way" has to solve the problem?

  • 1

    You’re right @Ricardopunctual. Reply updated!

Browser other questions tagged php sql binding

You are not signed in. Login or sign up in order to post.