I have a MySQL table with a field that is date format and can be NULL.
I'm updating an old PHP update file to use mysqli prepared statements and having problems saving date field values to the DB resulting in
Fatal error: Uncaught mysqli_sql_exception: Incorrect date value:.....
Minimum reproducible code below:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //force detailed report and die
require_once('../Connections/mysqli_connect.php');
//---------------------------on submit, do ------------------------------------
if ((isset($_POST["update"])) && ( $_POST["update"] == "editnoteform")) {
//Prepare dates
if(!isset($_POST['forecastdate'])){
$forecastdate = NULL;
} else {
$forecastdate = $_POST['forecastdate'];
}
//---------------------------update record ------------------------------------
$record_update = mysqli_prepare($conn, "UPDATE CONTNOTES SET FORECASTDATE=? WHERE NOTESID=?");
mysqli_stmt_bind_param($record_update, "di",
$_POST['forecastdate'],
$_POST['notesid']
);// bind parameters
mysqli_stmt_execute($record_update);// execute query
}
//---------------------------get existing data------------------------------------
$rs_notes = mysqli_prepare($conn, "SELECT NOTESID, FORECASTDATE FROM CONTNOTES WHERE NOTESID = ?");
mysqli_stmt_bind_param($rs_notes, "i", $_GET['NOTESID']);// bind parameters
mysqli_stmt_execute($rs_notes);// execute query
mysqli_stmt_bind_result($rs_notes, $NOTESID, $FORECASTDATE);// bind result variables
?>
<html>
<head>
<meta charset="utf-8"/>
</head>
<body>
<?php while (mysqli_stmt_fetch($rs_notes)) {?>
<form class="prompts" action="" method="POST" enctype="multipart/form-data" name="editnoteform" id="editnoteform" onSubmit="">
<input class="prompt" type="date" name="forecastdate" id="forecastdate" value="<?php if($FORECASTDATE <> NULL){echo date("Y-m-d",strtotime($FORECASTDATE));} ?>">
<input type="hidden" name="notesid" id="notesid" value="<?php echo $NOTESID; ?>">
<input type="hidden" name="update" value="editnoteform">
<input class="button" name="submit" id="submit" type="submit" value="Update">
</form>
<?php } ?>
</body>
</html>
<?php
mysqli_stmt_close($rs_notes);
mysqli_close ($conn);
?>
How is this an incorrect date value if the html field is a "date" type?
