How to convert date in Text box to MySQL DATETIME format

Question

Hi, I'm quite new to php and have been reading Larry Ullman book to develop a basic db site. I have used the YUI Calendar pop up date picker to add a date to a text field called"date". The date format it enters is eg Thursday, 7 May 2009

I have tried many different ways to try and enter the date in to mysql db but it remains at 00 00 00 00 00 00 This is the code related to the date field I have,

 // Check for a Date.
if (eregi ("^([0-9]{2})/([0-9]{2})/([0-9]{4})$", $_POST['date'],)) {
 $p = escape_data($_POST['date'],);
} else {
 $p = FALSE;
 echo '<p><font color="red">Please enter a valid Date!</font></p>';
}

 // Add the URL to the urls table.
 $query = "INSERT INTO urls (url, title, description, date) VALUES ('$u', '$t', '$d', '$p')";  
 $result = @mysql_query ($query); // Run the query.
 $uid = @mysql_insert_id(); // Get the url ID.

 if ($uid > 0) { // New URL has been added.

I think I have provided all pertinent information but again apologies if this isn't helpful and I will do my best to provide yo with any other information you may require. Thanks - Sean

Answer 1

You probably need to format the date in the way that mysql expects it for that datatype, or else it cannot recognize it. You could use a regex or similar method to extract the component parts and format it according to the format of the MySQL DATETIME type.

EDIT: See http://dev.mysql.com/doc/refman/5.0/en/datetime.html for said format. Also, you might be better off storing the date/time as a unix timestamp, as that usually is easier to maintain.

Answer 2

If the format that your date picker is passing in is "Thursday, 7 May 2009", then the strtotime() and date() functions should work to give you a valid date to pass to MySQL:

$p = date("Y-m-d H:i:s",strtotime('Thursday, 7 May 2009'));

If your database field is a DATE, you probably only want the "Y-m-d" part. If it's a DATETIME, you'll want the "H:i:s" as well.