In the MySQL table I have a field called date its type is called timestamp and the default is CURRENT_TIMESTAMP
. However, if I leave the field blank in MySQL I get an error. When I try to insert something into it like time()
I receive the date as 0000-00-00 00:00:00
.
<?php $name = ""; $email = ""; $subject = ""; $comments = ""; $nameError = ""; $emailError = ""; $subjectError = ""; $x = 5; function filterData($data) { $data = htmlspecialchars($data); $data = stripslashes($data); return $data; } $connection = mysql_connect('host', 'user', 'pass'); if (!$connection) { die('Could not connect: ' . mysql_error()); } $select_database = mysql_select_db("contact"); if (!$select_database) { echo "could not select database " . mysql_error(); } if ($_SERVER["REQUEST_METHOD"] == "POST") { //handles the name $name = filterData($_POST["name"]); if (empty($name)) { $nameError = "please don't leave the name field blank"; } //handles the email $email = filterData($_POST["email"]); if (empty($email)) { $emailError = "please don't leave the email field blank"; } //handles the subject $subject = filterData($_POST["subject"]); if (empty($subject)) { $subjectError = "please don't leave this field blank"; } $comments = filterData($_POST["comments"]); } $insertation = "INSERT INTO contactinfo (name, email, subject, date, comments) VALUES ('$name', '$email', '$subject', '', '$comments')"; $insertationQuery = mysql_query($insertation, $connection); if (!$insertationQuery) { echo "Could not process your information " . mysql_error(); } else { echo "Thank you for submitting the information"; } ?>
Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format. Tip: Also look at the GETDATE() function.
In addition to checking your table setup to confirm that the field is set to NOT NULL
with a default of CURRENT_TIMESTAMP
, you can insert date/time values from PHP by writing them in a string format compatible with MySQL.
$timestamp = date("Y-m-d H:i:s");
This will give you the current date and time in a string format that you can insert into MySQL.
Please try CURRENT_TIME()
or now()
functions
"INSERT INTO contactinfo (name, email, subject, date, comments) VALUES ('$name', '$email', '$subject', NOW(), '$comments')"
OR
"INSERT INTO contactinfo (name, email, subject, date, comments) VALUES ('$name', '$email', '$subject', CURRENT_TIME(), '$comments')"
OR you could try with PHP date
function here:
$date = date("Y-m-d H:i:s");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With