PHP not storing the date in MySQL table column

Question

I'm having a problem storing the date correctly in a MySQL database table under the column name 'publicationDate' via an html form.

 //  Contruct Of The Data
public function __construct($data=array()) {
if (isset($data['id'])) $this->id = (int) $data['id'];
if (isset($data['publicationDate'])) $this->publicationDate = (int) $data['publicationDate'];
if (isset($data['content'])) $this->content = $data['content'];
}

// Values To Post/Store For The Webpage Forms 
public function storeFormValues ($params) {
 $this->__construct($params);
 if (isset($params['publicationDate'])) {
   $publicationDate = explode ('-', $params['publicationDate']);
  if (count($publicationDate) == 3) {
    list ($y, $m, $d) = $publicationDate;
    $this->publicationDate = mktime (0, 0, 0, $m, $d, $y);
   }
  }
 }

The My SQL Column 'publicationDate' datatype is 'TIMESTAMP()' 'NOT NULL' default 'CURRENT_TIMESTAMP'. the date is to be stored & formatted '0000-00-00 00:00:00'.

The HTML form input for 'publicationDate' which is an edit form that is hidden and not to be edited is as:

<input type="hidden" name="publicationDate" id="publicationDate" value="<?php if ($results['article']->id ==true) { echo date("Y-m-d g:i:s", $results['article']->publicationDate);} else { echo date("Y-m-d g:i:s");}?>"/>

It'll store the date like this - '0000-00-00 00:00:00' which is 'yyyy-mm-dd hh:mm:ss' but only the year, month, & day will show correctly. The hour, minutes, & seconds will always appear in MySQL all zeros (00:00:00). This is obviously a problem when display/listing my data 'DESC' which is of course most recent being at the top of the list.

Alex Andrei · Accepted Answer · 2015-07-08 17:39:35Z

1

Since you want the mysql date format to be 'yyyy-mm-dd hh:mm:ss' you need to change the php date format.

Change date("Y-m-d g:i:s" into date("Y-m-d H:i:s" in both instances.

The g format displays the hour in 12-hour format of an hour without leading zeros while the H format displays the hour in 24-hour format with leading zeros.

Also when you construct the $publicationDate you create it with with hour=0, minute=0, second=0.

If you want to populate the $publicationDate with the time of the form's submission use this:

$publicationDate = mktime(date('H'), date('i'), date('s'), $m, $d, $y);

This will take the current hour, minute and second and the submitted month, day and year from the user.

You should actually assign actual times in your `storeFormValues function.

See here manual for mktime

edited Jul 8, 2015 at 17:39

answered Jul 8, 2015 at 7:38

Alex Andrei

7,2763 gold badges30 silver badges44 bronze badges

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

6 Comments

TonyM0str0 Over a year ago

How would I change adjust it this because I tried many changes with many different results. An example being that it'll store it 1969-12-31 00:00:00). I have no idea why I would be getting this result with the different changes I've tried.

TonyM0str0 Over a year ago

I did change the date in the 24-hour format but it's still not storing the hours for some reason. I believe it's in the 'storemformValues' function using mktime ().

TonyM0str0 Over a year ago

I've been messing around with this for a long time and it's driving me crazy.

Alex Andrei Over a year ago

are you actually getting the hour, min, sec values from the html form?

Alex Andrei Over a year ago

I updated my answer to allow current time plus submitted date

|

HenryTK · Accepted Answer · 2015-07-08 07:47:58Z

0

According to the documentation for the PHP date function (http://php.net/manual/en/function.date.php) the g in g:i:s would be 12-hour format of an hour without leading zeros. It needs to be H, which is 24-hour format of an hour with leading zeros.

answered Jul 8, 2015 at 7:47

HenryTK

1,2979 silver badges12 bronze badges

1 Comment

TonyM0str0 Over a year ago

I changed this and still recieve the same results when storing the date.

Collectives™ on Stack Overflow

PHP not storing the date in MySQL table column

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related