CODEWITHSUNDEEP
phphtmlmysqldatetimestamp
TonyM0str0
TonyM0str0

Reputation: 55

PHP not storing the date in MySQL table column

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.

Upvotes: 2

Views: 983

Answers (2)

Alex Andrei
Alex Andrei

Reputation: 7283

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

Upvotes: 1

HenryTK
HenryTK

Reputation: 1287

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.

Upvotes: 0

Related Questions