Updating mysql date column become 0000-00-00 but with insert it work well

Question

No update with date field success. All update made become 0000-00-00. Insert is working propery with the same value. Any help to fix or explanation will be admirable

Answer 1

Additionally, to Calvin's excellent suggestion, date values are commonly wrapped in single quotes, in your query;

$query="UPDATE mytable SET start_date= '$s_date' WHERE Id=12";

Answer 2

Show your code would me others understand your question.

Answer 3

Great, now we can understand your issue, with the code showing.

Post value cannot be directly put into query as date value, you need to convert to sql date format.

Try

$s_date=date("Y-m-d H:i:s",strtotime($s_date));
Or
$s_date=date("Y-m-d",strtotime($s_date));

Depends the date is datetime or date format

Answer 4

/* this is the full code*/
<?php
include ("connection.php");
if(isset($_post['btn-save'])){
$s_date=$_post['start_date'];
try{
$query="UPDATE mytable SET start_date= $s_date WHERE Id=12";

$stmt= $conn->prepare($query);
$stmt->execute();
}catch(PDOException $e){
echo $query." ".$e->getMessage();}
}
?>

//html form
<form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method= "post">
<input type="date" name="start_date" />

<button type="submit" name="btn-save">Save</button>
</form>

Answer 5

Thanks for your intervention Mr. Calvin

Answer 6

Thanks Ipang for your intervention, by surounding $s_date with quotes it works