Insert date&time using php into mysql

[tariq_wajiha]

Hello everyone,
I have a form in which there is create date and publishing date field the date and time is selected from the calender placed next to these text field and is a javascript calender. In database createdate and publishingdate fields type is datetime and the query i am using is given below:

PHP Code:

$title = $_POST['title'];
    $category_name = $_POST['cat_name'];
    $published = $_POST['published'];
    $description = $_POST['description'];
    $createdate = $_POST['createdate'];
    $publishingdate = $_POST['publishingdate'];
    $showtitle = $_POST['showtitle'];
    $accesslevel = $_POST['accesslevel'];
    
    $sql = "INSERT INTO article
    (`title` ,`category_name` ,`published`, `description`, `createdate`, `publishingdate`, `showtitle`, `accesslevel`
) values
('$title', '$category_name', '$published', '$description', '$createdate', '$publishingdate', '$showtitle', '$accesslevel')";


When i fill the form and save it all the input is saved in the database but the datetime is saved with a null value--->0000-00-00 00:00:00.


One more thing i would like to mention here is when i keep the datatype of createdate and publishing date to text the date and time is stored as they are mentioned in the input field through javascript calender.


Can anyone help me out how can i insert the datetime when my datatype is datetime.

Thanks

[scragar]

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Check the format of the date-time you are attempting to store.

[tariq_wajiha]

4-11-2009 12:42:24

This is how it is picking the date and time, the time is current time of my system

[scragar]

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format.

The answer to your question is right there, the format is not what MySQL accepts.

Personally I've always used timestamps instead of DATETIME, so much easier to work with.
Storing/Retrieving with timestamps

function toTimestamp($str){
  list($day, $month, $year, $hours, $mins, $secs) = preg_split('/[^\d/', $str);
  return mktime ($hours, $mins, $secs, $month, $day, $year);
}
function fromTimestamp($stamp){
  return date('j-n-Y H:i:s', $stamp);
}

Storing/Retrieving with DATETIME

function toDateTime($str){
  list($day, $month, $year, $hours, $mins, $secs) = preg_split('/[^\d/', $str);
  $tmp = mktime ($hours, $mins, $secs, $month, $day, $year);
  return date('Y-m-d H:i:s', $tmp);
}
function fromDateTime($dt){
  list($year, $month, $day, $hours, $mins, $secs) = preg_split('/[^\d/', $str);
  $tmp = mktime ($hours, $mins, $secs, $month, $day, $year);
  return date('j-n-Y H:i:s', $tmp);
}

You might also want to try playing around with StrToTime, although I do find it slower.