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";
}
?>
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");
The DEFAULT
value of a column in MySql is used only if it isn't provided a value for that column.
So if you
INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', '', '$comments')
You are not using the DEFAULT
value for the column date
, but you are providing an empty string, so you get an error, because you can't store an empty string in a DATETIME
column.
The same thing apply if you use NULL
, because again NULL
is a value.
However, if you remove the column from the list of the column you are inserting, MySql will use the DEFAULT
value specified for that column (or the data type default one)
You can try wiht TIMESTAMP(curdate(), curtime()) for use the current time.
You do not need to insert the current timestamp manually as MySQL
provides this facility to store it automatically. When the MySQL
table is created, simply do this:
TIMESTAMP
as your column type Default
value to CURRENT_TIMESTAMP
insert
any rows into the table without inserting any values for the time
columnYou'll see the current timestamp
is automatically inserted when you insert a row. Please see the attached picture.
If you have a specific integer timestamp to insert/update, you can use PHP date() function with your timestamp as second arg :
date("Y-m-d H:i:s", $myTimestamp)
$insertation = "INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', CURRENT_TIMESTAMP(), '$comments')";
You can use this Query. CURRENT_TIMESTAMP
Remember to use the parenthesis CURRENT_TIMESTAMP()
Source: Stackoverflow.com