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

Question

$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

Answer 1

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.

Answer 2

You can use NOW() :

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