Change mysql timestamp field via php

Question

I have the following database:

CREATE TABLE Perdidos` (
  `idPerdidos` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dataEncontrado` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `descricao` VARCHAR(45) NOT NULL DEFAULT 'Sem descrição',
  `quemEncontrou` VARCHAR(45) NOT NULL DEFAULT 'desconhecido',
  `localEncontrado` VARCHAR(45) NOT NULL DEFAULT 'Sem local',
  `comValorSemValor` INT(1) UNSIGNED NOT NULL,
  `estadoDoPerdido` INT(1) NOT NULL DEFAULT '1',
  `dataEntrega` TIMESTAMP NULL DEFAULT NULL,
  `destinoDoPerdido` INT NOT NULL DEFAULT '1',
  `Utilizadores_POR` INT UNSIGNED NOT NULL,
....

php code after connection:

$sql = "UPDATE perdidos SET

            dataEntrega =  ?
            WHERE idPerdidos = ?";

    $stmt = $connection->prepare($sql);


    $stmt->bindParam(1, $_POST["CURRENT_TIMESTAMP()"]);
    $stmt->bindParam(2, $_POST["idPerdidos"]);

    $stmt->execute();

What I intend to do is to finish the "lost" this stick to the current date.

Answer 1

You can set this directly in the database when creating the table. Change the row:

Of:

dataEntrega TIMESTAMP NULL DEFAULT NULL,

To:

dataEntrega DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

That way in PHP pass only the parameter idPerdidos

$sql = "UPDATE perdidos SET
        WHERE idPerdidos = ?";

$stmt = $connection->prepare($sql);

$stmt->bindParam(1, $_POST["idPerdidos"]);

$stmt->execute();

Answer 2

changed the field in Mysql from TIMESTAMP() to date type.

php was making a mistake in $_POST because I do not enter any date in the form.

was like this:

Else {

    $datetime = time();

    $sql = "UPDATE h528fbz1_perdidos.perdidos SET

            dataEntrega =  ?
            WHERE idPerdidos = ?";

    $stmt = $connection->prepare($sql);


    $stmt->bindParam(1, date('Y-m-d H:i:s'), $datetime);
    $stmt->bindParam(2, $_POST["idPerdidos"]);

    $stmt->execute();