CODEWITHSUNDEEP
phpmysqldatedatetime
Freddy
Freddy

Reputation: 867

date() which echo's correct date and time format, but doesn't store it correctly in database

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?

Upvotes: 0

Views: 98

Answers (3)

Birendra Gurung
Birendra Gurung

Reputation: 2233

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');

Upvotes: 0

Neobugu
Neobugu

Reputation: 333

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

Upvotes: 1

brunomayerc
brunomayerc

Reputation: 325

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.

Upvotes: 0

Related Questions