SQL将我的输入日期转换为0000-00-00和时间00:00:17 [关闭]

Question

I've done a ton of research and tried a bunch of things, but no luck, so I guess I should ask here.

Two things. I have the following code to pick a date and time.

<input type="date" name="conDate">
<input type="time" name="conTime">

And then the following to validate and convert it to store in SQL.

$conDate = mysqli_real_escape_string($conn, isset($_POST['conDate'])) ? $_POST['conDate']: null;
$conTime = mysqli_real_escape_string($conn, isset($_POST['conTime'])) ? $_POST['conTime']: null;

$conDate = trim($conDate);
$conTime = trim($conTime);
$conDate = date('Y-m-d', strtotime(str_replace('/', '-', $conDate)));
$conTime = date("H:i", strtotime($conTime));

$sqlSum = "INSERT INTO software.concert(
                        concertTitle, concertDesc, concertLocation, concertDate, concertTime, concertHost, concertWallet, concertRevenue, concertAgeRes)
                        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";

                        $stmt = mysqli_prepare($conn, $sqlSum);
                        mysqli_stmt_bind_param($stmt, "ssddddddd", $title, $desc, $location, $conDate, $conTime, $hostID, $wallet, $revenue, $ageR);
                        mysqli_stmt_execute($stmt) or die(mysqli_error($conn));
                        mysqli_stmt_close($stmt);

concertDate (and concertTime) are the correct names of the fields, and is of field type DATE (and TIME(0)), and I've frequently tried to debug, but for some weird reason the SQL table consistently gets 0000-00-00 as the Date value though the inputted value is 2016-08-21 (and 00:00:17 for 17:30). I've tried removing str_replace, even having the table as varchar(10) to input the date but even that didnt help me.

What can I do to prevent 0000-00-00 and 00:00:17 from being inputted in the database, and ensure the proper values or 2016-08-21 (yyyy-mm-dd) and 17:30 (hh:mm)?

(just a side note, all other values are inputting to the database, so the statement itself checks out, just not for the date and time. I get an AI id, and i get the title, desc, location, etc all in, it's just date and time.)

Answer 1

Don't bind the date/time parameters as doubles but strings
...just bind them all as strings and let the MySQL server's type system handle it.

if ( !mysqli_stmt_bind_param($stmt, "sssssssss", $title, $desc, $location, $conDate, $conTime, $hostID, $wallet, $revenue, $ageR) ) {
    throw new mysqli_sql_exception('bind failed');
}