Member Avatar for nathanpacker

Hey, I've got a form that passes the date onto a php script, which posts all the form entries into a database. One of those fields happens to be a date field. I have it going into a column in a mysql database that has the DATETIME datatype, but no matter how I work the code, it always goes into the database as 0000-00-00 00:00:00. I know I'm passing the date correctly through the form, because I've echo'd out the variable and it shows the correct date. I'm thinking my problem is on the mysql database, but can't figure it out!

For now, I've just changed the datatype to VARCHAR in that field, and it's working, but I'd very much like to use the DATETIME datatype. Any ideas?

  • 3 Contributors
  • 3 Replies
  • 226 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by nathanpacker

Dani AI

Generated

A short, practical addendum to what and discovered: the problem is almost always a format or quoting mismatch between what PHP sends and what MySQL expects for a DATETIME column. PHP can echo a value that looks correct, but if the final SQL contains an unquoted, malformed, or empty string, MySQL will silently store a zero/invalid datetime in non‑strict mode. Converting/normalizing the value in PHP before the INSERT — or using a prepared statement and binding a properly normalized value — prevents this.

Quick checklist for future debugging (covers gaps not shown in the thread):

  • Capture and inspect the exact INSERT statement that reaches MySQL (or log it).
  • Check the DB error output (mysqli_error / PDOException) rather than assuming success.
  • Confirm the column definition with SHOW CREATE TABLE table_name; (NULL/NOT NULL and default behavior).
  • Avoid inserting an empty string into a DATETIME column; insert NULL if no value is intended.
  • Be aware of MySQL SQL_MODE: non‑strict installs may coerce invalid dates to zero; strict modes will throw an error.

Examples you can apply immediately:

# normalize in PHP, then use a prepared statement (PDO)
$dt = new DateTime($rawInput);                  // normalize/parsing step
$stmt = $pdo->prepare('INSERT INTO events (title, event_time) VALUES (:t, :d)');
$stmt->execute([':t' => $title, ':d' => $dt->format('Y-m-d H:i:s')]);
# let MySQL parse a different input format
INSERT INTO events (event_time)
VALUES (STR_TO_DATE(:raw_input, '%m/%d/%Y %H:%i'));

Final notes: prefer validating input with DateTime::createFromFormat so malformed dates are caught before hitting the DB. Use prepared statements for safety and to avoid quoting issues. Mentioning earlier — providing a sample of the raw input and the actual SQL is the quickest way to spot the mismatch.

Recommended Answers

All 3 Replies

Member Avatar for stymiee

Please post a sample of the data you are attempting to enter.

Member Avatar for digital-ether

Try something like:

function mysql_date($year, $month, $day, $hour, $min, $sec) {
	return date("Y-m-d H:i:s", mktime($hour, $min, $sec, $month, $day, $year));
}
Member Avatar for nathanpacker

Try something like:

function mysql_date($year, $month, $day, $hour, $min, $sec) {
	return date("Y-m-d H:i:s", mktime($hour, $min, $sec, $month, $day, $year));
}

That worked perfectly. I didn't realize I was passing

date("Y-m-d")

to the database and it was expecting

date("Y-m-d H:i:s")

Hey, I learn something new on this forum every day. And I also found a chart on the php website that shows all the time tags to put in. Thanks for the info!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.