Which UPDATE statement to change my TIMESTAMP only when i change my char to '1'? PHP, mysq-CodePudding

This is my table in mysql db:

CREATE TABLE admins (
id int NOT NULL AUTO_INCREMENT,
bez varchar(55) DEFAULT 'unknown',
type varchar(7) NOT NULL DEFAULT 'unknown',
exist char NOT NULL DEFAULT '0',
tm TIMESTAMP default CURRENT_TIMESTAMP;

PRIMARY KEY ('id')
);

I want to update my timestamp only when i set exist to '1'. Not when I change any column.

This is my UPDATE statement:

<?php
    if ( isset($_GET["id"]) ) {
        $id = $_GET["id"];
    
        $sql = "UPDATE admins SET exist='1' WHERE id=$id";
        $con->query($sql);
    }
    
    header("location: /index.php");
    exit;
    ?>

I tried different ways to combine it with tm but it didnt worked! I tried stuff like this:

    if ( isset($_GET["id"]) ) {
        $id = $_GET["id"];

$tm = date('Y-m-d H:i:s');

        $sql = "UPDATE admins SET exist='0', tm=$tm WHERE id=$id";
}

using AND didnt worked.

I tried ON UPDATE CURRENT_TIMESTAMP in sql but it didnt works for me. Because i want only new tm if exist is '1'.

I tried this in sql:

DELIMITER $$

CREATE TRIGGER trigger_name
    AFTER UPDATE
    ON admins FOR EACH ROW
BEGIN
  IF NEW.exists = 1 THEN 
    SET NEW.tm = NOW();
  END IF;
END$$    

DELIMITER ;

but i get a sql error message:

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

so i tried to use BEFORE instead of AFTER but i didnt changed the tm at all.

Do you have any ideas? What can i do to solve my issue?

Goal: UPDATE tm when setting exist to '1'

dont tell me that i am open to sql injections! Thanks for your help!

CodePudding user response：

Looks like a trigger will solve this for you.

If we make it a BEFORE UPDATE which is allowed to update the NEW values where an AFTER UPDATE trigger is not, as per your error message, and remove a couple of syntax errors, the trigger might look like this

DELIMITER $$
CREATE TRIGGER upd_date_if_exists_is_one
    BEFORE UPDATE ON admins FOR EACH ROW
    BEGIN
        IF NEW.`exist` = 1 AND old.`exist` <> 1 THEN 
            SET NEW.tm = NOW();
    END IF;
END$$    
DELIMITER ;

If you leave the tm TIMESTAMP default CURRENT_TIMESTAMP; so that will set the tm column when the initial INSERT is done, but not update the tm when any subsequent updates are done.

CodePudding user response：

UPDATE: Added PDO to account for SQL Injection Attacks.

Change your trigger to BEFORE UPDATE:

DELIMITER $$

CREATE TRIGGER bu_if_exists
    BEFORE UPDATE
    ON admins FOR EACH ROW
BEGIN
  IF NEW.exist = '1' THEN 
    SET NEW.tm = CURRENT_TIMESTAMP;
  END IF;
END$$    

DELIMITER ;

Declare a constant for exist so you can change it when you want. UPDATE your query, passing in your exist value by your id:

  <?php

    if (isset($_GET["id"]) ) {
    
    $id = $_GET["id"];
    $exist = '1'; //change this to '0' if you do not want to update the timestamp
    
    try {

        $conn = new PDO('mysql:host='.HOST.';dbname='.DATABASE,USERNAME,PASSWORD); 
        
        $sql = "UPDATE `admins` SET `exist` = :exist WHERE `id` = :id";
            
        $statement = $conn->prepare($sql);
        $statement->bindValue(":exist", $exist);
        $statement->bindValue(":id", $id);
        $statement->execute();

        $conn = null; //Disconnect Connection
            
    } catch(PDOException $e) {
        echo $e->getMessage();
    }


header("location: /index.php");
exit;
?>

See Fiddle.