Page 1 of 1

Update Date

Posted: Mon Aug 30, 2004 5:01 pm
by TNIDBMNG
I have a text box which stores a date from a table I have in MySQL. I want the user to be able to change the date if they would like which works fine, the problem I'm having is I have to display the date in the text box as dd-mmm-yyyy. If the user updates another field and leaves the date as that format MySQL doesn't recognize the date so it changes the date to NULL. I tried the Date() function but it changes the date to 1969-12-31. How can I change the format of the text box to be MySQL's format before the database is updated? Here is the code I currently have.

Code: Select all

<?php
$p_priority=date("Y-m-d",$_POST[$priorityfield]);
$sql="UPDATE tblITPriorities SET dueDate='".$duedate."', priority='".$p_priority."', completed='".$p_completed."', dateCompleted='".$datecompleted."' WHERE ID=".$p_id.";";
mysql_query($sql,$cnx);
?>

Posted: Mon Aug 30, 2004 5:18 pm
by feyd
it's easier to actually have 3 inputs, one for month, day, and year. Each can be a textbox, but it's often easier to have drop-downs for at least month and day. Although the validation routine remains the same for all.

Posted: Tue Aug 31, 2004 7:15 am
by timvw
For each timestamp that will be outputted, i add this to my SELECT clause
DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i) AS timestamp

This way the user is presented a "nice" date ;)


But if you want to use a unix time to put in a mysql timestamp use:

FROM_UNIXTIME($date)

Posted: Tue Aug 31, 2004 11:51 am
by TNIDBMNG
Thanks Tim but I'm not outputting the data I'm trying to update the data in the database. I currently use date_format for outputting but does it work the same way when updating the database?

Posted: Tue Aug 31, 2004 11:59 am
by markl999
What is the MySQL column type holding the date ? datetime, timestamp etc.. ?

Posted: Tue Aug 31, 2004 12:01 pm
by TNIDBMNG
datetime

Posted: Tue Aug 31, 2004 12:16 pm
by markl999
A datetime is in YYYY-MM-DD HH:MM:SS format so you need to use:
date('Y-m-d h:i:s')