How to place MySql DATETIME results into HTML input with type datetime-local

Question

i stored date using <input type="datetime-local"> in the database now i want to get back the date in <input type="datetime-local"> for update purpose.

i am doing

$query="select * from exam_schedule where subject_name='$name' AND class_id='$schedule_id' limit 1";
$result= mysqli_query($connection,$query);
while ($read_all_data = mysqli_fetch_assoc($result))
{ 

echo "date comming from datebase::".$date=$read_all_data['date_and_time']."<br>";
echo "duration comming from datebase::".$duration=$read_all_data['duration'];

$duration=$read_all_data['duration'];
echo "<form method='post' action='edit_schedule.php'>";
echo "<input type='hidden' name='id' value='$id'>";
echo  "<tr><th><input type='datetime-local' name='date' value='$date'> </th>";
echo  "<th><input type='datetime-local' name='duration' value='$duration'> </th>";
echo <input type='submit' name='update' value='update'> </form></th></tr>";
}

when i echo the $date and $duration it shows me values but when i put value="$date" it doesnot show me date from database.

Answer 1

The problem is that the datetime-local input type expects the date to be in this format: yyyy-mm-ddThh:mm but you are supplying it the default DATETIME format created by the database which is yyyy-mm-dd hh:mm:ss.

Demo

In order to change it, you need to change your SQL query to this:

SELECT *, DATE_FORMAT(date_and_time, '%Y-%m-%dT%H:%i') AS custom_date 
FROM exam_schedule 
WHERE subject_name='$name' 
AND class_id='$schedule_id' 
LMIT 1

so now in your results, you can then use the custom_date to prefill your input.

<input type='datetime-local' name='date' value='<?php echo $row['custom_date'] ?>' >

Answer 2

If you need to convert the time in PHP instead of MySQL:

1. Convert the MySQL datetime string into a PHP timestamp
2. Apply a PHP date format mask to the PHP timestamp, where the format matches the HTML5 datetime-local format

PHP code sample:

const HTML_DATETIME_LOCAL = 'Y-m-d\TH:i'; //escape the literal T so it is not expanded to a timezone code
$php_timestamp = strtotime($mysql_date_string);
$html_datetime_string = date(HTML_DATETIME_LOCAL, $php_timestamp);
echo "<input type='datetime-local' name='date' value='{$html_datetime_string}'>";