save datetime to mysql with php

Question

hellow, im creating form with datetimebox easyui, like this :

<input id="dt" name ="po_receive" class="easyui-datetimebox" name="date_time_pick" data-options="required:true,showSeconds:false" style="width:150px">

and i want to save it to mysql with datetime type database, but the result is 0000-00-00 00:00:00. The javascript easyui-datetimebox works perfectly fine.

this is my save data code :

<?php
$po_receive = $_REQUEST['po_receive'];

$sql = "insert into db(po_receive) values ('$po_receive')";
$result = @mysql_query($sql);
if ($result){
    echo json_encode(array(
        'id' => mysql_insert_id(),
        'po_receive' => $po_receive
    ));
} else {
    echo json_encode(array('errorMsg'=>'Some errors occured.'));
}

?>

but the result is 0000-00-00 00:00:00, is that because datetimebox value format is "01/03/2016 07:53:09" ? example datetimebox :datetimebox

Answer 1

you need to reformat the date before inserting to DB

$dc=date_create($po_receive);
$formated_date=date_format($dc,"Y-m-d"); //"Y-m-d" is the date format
$sql = "insert into db(po_receive) values ('$formated_date')";

Please see this link, for different types of date format http://php.net/manual/en/function.date.php

Answer 2

ok, tq guys its work. i need put function date("Y-m-d H:i:s", strtotime())

Tq all.

Answer 3

the result is 0000-00-00 00:00:00, is that because datetimebox value format is "01/03/2016 07:53:09"

That's because you're trying the insert the datetime in an incorrect format. MySQL's DATETIME takes the following format, YYYY-MM-DD HH:MM:SS.

Here's the reference:

• DATETIME

Use createFromFormat() and getTimestamp() methods of DateTime class to convert your time into unix timestamp, and then use strftime() to convert the unix timestamp to MySQL format.

Here are the references:

• DateTime class
• strftime()

So your code should be like this:

<?php

    $po_receive = $_REQUEST['po_receive'];
    $unixdatetime = DateTime::createFromFormat('d/m/Y H:i:s', $po_receive)->getTimestamp();
    $po_receive = strftime("%Y-%m-%d %H:%M:%S",$unixdatetime);

    $sql = "insert into db(po_receive) values ('$po_receive')";
    $result = @mysql_query($sql);
    if ($result){
        echo json_encode(array(
            'id' => mysql_insert_id(),
            'po_receive' => $po_receive
        ));
    } else {
        echo json_encode(array('errorMsg'=>'Some errors occured.'));
    }

?>

Sidenote: Please don't use mysql_ database extensions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or PDO extensions instead. And this is why you shouldn't use mysql_ functions.

Answer 4

You need to convert your input date as per MYSQL datetime standard.

Replace:

$po_receive = $_REQUEST['po_receive']; 

With:

$po_receive = date ("Y-m-d H:i:s",strtotime($_REQUEST['po_receive']));