+ 3

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

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

23rd Feb 2019, 5:28 AM
Aphro10
Aphro10 - avatar
6 odpowiedzi
+ 9
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";
23rd Feb 2019, 10:52 AM
Ipang
+ 6
Show your code would me others understand your question.
23rd Feb 2019, 6:18 AM
Calviղ
Calviղ - avatar
+ 4
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
23rd Feb 2019, 9:51 AM
Calviղ
Calviղ - avatar
+ 3
/* 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>
23rd Feb 2019, 7:05 AM
Aphro10
Aphro10 - avatar
+ 3
Thanks for your intervention Mr. Calvin
23rd Feb 2019, 10:44 AM
Aphro10
Aphro10 - avatar
+ 3
Thanks Ipang for your intervention, by surounding $s_date with quotes it works
23rd Feb 2019, 12:01 PM
Aphro10
Aphro10 - avatar