johnbumble johnbumble - 6 months ago 19
PHP Question

How to insert timestamp into my mysql table

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('', '', '');
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";
}



?>

Answer

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 strong 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.