0

I have the following snippet of code:

date_default_timezone_set('UTC');
if (!isset($_POST['secret']) && $post_msg != "" ) { // checkbox unchecked processing...
    // Checkbox is selected
    $date_of_msg= date('l jS F Y h:i');
    $msg_sent_by  = $username;
    $insert_query = "INSERT INTO user_thoughts VALUES ('','$post_msg','$date_of_msg','','' ,'$attach_name','$msg_sent_by','yes')";
    $run_query = mysqli_query($connect, $insert_query) or die(mysqli_error());
}

When I echo $date_of_msg, the date and time will print out as expected, but when the following INSERT query above is ran, the field in the db will store 0000-00-00 00:00:00.

The field which will store $date_of_msg is called post_details and is of type datetime. I am aware that there is a function called date_to_str and have seem questions related to it, such as this one. But the answers in that question are converting manually inputted dates, whereas I want to get the time when a user makes a post. I think the solution is to use the date_to_str function when inserting the $date_of_msg variable? But I am unable to understand how it works?

Community
  • 1
  • 1
Freddy
  • 683
  • 4
  • 35
  • 114
  • 3
    `l jS F Y h:i` is not mysql's datetime format. Why not use `now()`? – chris85 Mar 02 '16 at 15:59
  • 3
    MySQL expects dates to be in the format `YYYY-MM-DD hh:mm:ss` – Barmar Mar 02 '16 at 16:00
  • What is the format of your destination column? If it is DATE format then you need to change your input layout as detailed by the comments above. Also your `MySQLi_error()` needs to contain the `$connect` value. – Martin Mar 02 '16 at 16:01

3 Answers3

1

Your 

date('l jS F Y h:i');

is sending a result like:

Wednesday 2nd March 2016 10:00

so you are getting that value 0000-00-00 00:00:00 as a mismatch of datetime

Change it to:

date('Y-m-d h:i:s');

or format the date to something you need using this doc. http://php.net/manual/en/function.date.php

Machavity
  • 30,841
  • 27
  • 92
  • 100
Neobugu
  • 333
  • 6
  • 15
0

This is probably happening because your web server (apache im guessing) configuration is different than your mysql config.

So that's why you're experiencing some confusion.

There are a few ways to go about this, but it will require you to choose a format and run with it.

  1. When inserting the record to mysql, tell it what format you want to use.

    $query_manual = "INSERT INTO dateplayground (dp_name, dp_datetime) VALUES ('DATETIME: Manual DateTime', '1776-7-4 04:13:54')";

  2. Research mysql and apache date time configurations and fix both of them so they have the same exact date time format.

brunomayerc
  • 295
  • 1
  • 3
  • 8
0

In mysql datetime field, you can insert only the date time of the format Y:m:d H:i:s

Please use the datetime with the same format

$date_of_msg = date('Y:m:d H:i:s');
Birendra Gurung
  • 2,168
  • 2
  • 16
  • 29