How to add a timestamp of a prepared stmt to mysql database when code is run?

$stmt = $conn->prepare("INSERT INTO users (username,created) VALUES (?,?)");
$stmt->bind_param("ss", $_POST['username-reg','whatdoiputhere?']);
$stmt->execute();

I want to add a "now" date/timestamp that can also be inserted simultaneously so that i can keep track of when users create accounts. is there a function that i need to write/reference to get the current date? or is there an already known function that i just need to reference? i am new to php, sorry

2 answers

  • answered 2020-02-18 22:05 Joffrey Schmitz

    You can use NOW() :

    $stmt = $conn->prepare("INSERT INTO users (username,created) VALUES (?, NOW())");
    $stmt->bind_param("s", $_POST['username-reg']);
    $stmt->execute();
    

  • answered 2020-02-18 22:05 GMB

    You can leverage MySQL's timestamp initialization feature.

    This works by adding a timestamp column to the table with the default current_timestamp option:

    create table users (
        user_id int primary key auto_increment,
        username varchar(50),
        ts_created timestamp default current_timestamp
    );
    

    With this set-up in place, you don't have to worry about that column anymore. Just ignore it when you insert, and the database will automatically set it as needed:

    $stmt = $conn->prepare("INSERT INTO users (username) VALUES (?)");
    $stmt->bind_param("ss", $_POST['username-reg']);
    $stmt->execute();
    

    Note: if you also want the timestamp to be updated when the record is modified, you can add the on update current_timestamp to the definition of the column.