PHP posting datetime to mysql

Question

I can't figure this out, I'm sure it's very easy and I'm approaching it the wrong way. I have a mysql items db with a column name datePur and type datetime.

Sample data;

+---------------------+
| datePur             |
+---------------------+
| 2015-11-03 11:05:00 |
+---------------------+
| 2014-12-02 07:14:11 |
+---------------------+
| 2015-08-01 11:09:34 |
+---------------------+

I have two forms in my web app, view and edit.

The view form retrieves the db data and displays the date in a read only table field in the following format; (03-11-2015)

<td><input type='text' name='datePur' value="<?php echo date("d-m-Y", strtotime($datePur)); ?> disabled"></td>

This works as expected.

My edit form retrieves the same data, however this time allowing the user to edit the date if necessary. The date is displayed to the user in the following format (not what I want); (2015-11-03 11:05:00)

<td><input type='text' name='datePur' value="<?php echo $datePur; ?>"></td>

I use the following code to post the data to the database;

edit.php

if (isset($_POST['btn-update'])) {
    $datePur = ($_POST['datePur']);
}

function.php

public function update($datePur) {
        try {
            $stmt = $this->db->prepare("UPDATE items SET datePur=:datePur");
            $stmt->bindParam(":datePur", $datePur);
            $stmt->execute();
            return true;
        } catch (PDOException $e) {
            echo $e->getMessage();
            return false;
        }
    }

The form will only submit if I enter the date in the format Y-M-d h:i:s - the same format as it's presented. I can't for example enter 03-11-2015, as the form won't submit.

I'd like to keep the date format consistent to end users. I'd like the datetime always to be displayed to the end-user in the format d-m-Y. However I would like it inserted to the database in the format Y-M-d h:i:s

Any advice is appreciated.

Did you even try: php.net/manual/en/function.date.php

AbraCadaver
– AbraCadaver

2016-01-29 16:39:10 +00:00
Commented Jan 29, 2016 at 16:39 — AbraCadaver
– AbraCadaver, Commented Jan 29, 2016 at 16:39

James · Accepted Answer · 2016-01-29 16:39:14Z

1

Just convert the date again when they update.

if (isset($_POST['btn-update'])) {
    $datePur = date('Y-m-d', strtotime($_POST['datePur']));
}

However, I usually have issues with it converting the date correctly, so I wrote a function to convert the date.

if (isset($_POST['btn-update'])) {
    $datePur = convertDate ($_POST['datePur']);
}

function convertDate ($date) {
    $date = str_replace("/", "-", $date);

    $date = explode("-", $date);

    return $date[2]."-".$date[0]."-".$date[1];
}



function convertTimestamp ($date) {
    $date = str_replace("/", "-", $date);

    $datetime = explode(" ", $date);
    $date = explode("-", $datetime[0]);
    $time = explode(':', $datetime[1]);

    $return = $date[2]."-".$date[0]."-".$date[1]." ".$time[0].":".$time[1].":00";

    if ($datetime[2] == 'PM' && $time[0] > 12) {
        $return = date('Y-m-d H:i:s', strtotime('+12 hours', strtotime($return)));
    }elseif ($datetime[2] == 'AM' && $time[0] == 12) {
        $return = date('Y-m-d H:i:s', strtotime('-12 hours', strtotime($return)));
    }

    return $return;
}

The function will work whether you use "/" or "-" as your date separator, but has to have a date in the format of "m-d-Y" as the initial date.

answered Jan 29, 2016 at 16:39

James

3,8056 gold badges51 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AbraCadaver Over a year ago

Using / the format is m/d/Y. 1/29/2016 is a valid date 1-29-2016 is not. When using - the format is d-m-Y as 29-1-2016.

James Over a year ago

@AbraCadaver My function can accept both 01/29/2016 or 01-29-2016. It does not use the date function so wouldn't matter if PHP would see it as a valid date. It just depends on the user's preference is all.

James Over a year ago

@johnny_s No problem, I hope it helps.

szg28 · Accepted Answer · 2016-01-29 16:43:39Z

The easiest way to display and save date by helper functions or methods.

// input format: d-m-Y H:i:s
function saveDate( $date_str )
{
   list( $date, $time ) = explode( ' ', $date_str );
   list( $d, $m, $y ) = explode( $date );
   return $y.'-'.$m.'-'.$d.' '.$time;
}

and

// input format (from DB): Y-m-d H:i:s
function loadDate( $date_str, $need_time = true )
{
   list( $date, $time ) = explode( ' ', $date_str );
   list( $y, $m, $d ) = explode( $date );
   return $d.'-'.$m.'-'.$y.' '.( $need_time ? $time : '' );
}

That's all. You can display the time with this code:

<td><input type='text' name='datePur' value="<?php echo loadDate( $datePur ); ?>"></td>

... and save:

$stmt = $this->db->prepare("UPDATE items SET datePur=:datePur");
        $stmt->bindParam(":datePur", saveDate( $datePur ) );

Abdulla Nilam · Accepted Answer · 2016-01-29 16:39:38Z

0

Do a date("Y-m-d H:i:s", strtotime($datePur)) before binding it

edited Jan 29, 2016 at 16:39

Abdulla Nilam

39.8k18 gold badges85 silver badges115 bronze badges

answered Jan 29, 2016 at 16:38

Andreas Schrammel

4631 gold badge6 silver badges11 bronze badges

Collectives™ on Stack Overflow

PHP posting datetime to mysql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related