Inserting DATETIME to SQL from PHP

Question

I can't seem to store the datetime to the database.

public function add_post($username, $content) {
    date_default_timezone_set("Asia/Manila");
    $date = date("m/d/Y h:i A");
    $final = strtotime($date);
    $time_posted = date("m/d/Y h:i A", $final);

    $sql =" INSERT INTO POSTS (username, post_content, time_posted)
            VALUES ('$username','$content','$time_posted')";

        return $this->db->query($sql);
}

It looks like this in the database 0000-00-00 00:00:00

Here is my posts table: Posts table

score 3 · Answer 1 · answered Mar 31 '17 at 18:27

3

The date-time field should be formatted as:

$time_posted = date("Y-m-d H:i:s", $final);

answered Mar 31 '17 at 18:27

Dhruv Saxena

1,336
2
12
29

Thanks. Glad it worked. One minor suggestion though - unrelated to datetime / timestamp. Please do take a look at [MySQLi](http://php.net/manual/en/mysqli.prepare.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) Prepared Statements to write queries in a rather secure way. If this was a live system, then it might be exposed to [SQL Injection Attacks](http://stackoverflow.com/q/60174/2298301) over the internet. – Dhruv Saxena Mar 31 '17 at 18:37
Thank you for the suggestion :) – JM Lontoc Apr 01 '17 at 10:40

score 1 · Answer 2 · answered Mar 31 '17 at 18:32

Help this code for you,

public function add_post($username, $content) {
   date_default_timezone_set("Asia/Manila");
   $date = date("m/d/Y h:i A");
   $final = strtotime($date);
   $time_posted = date("Y-m-d H:i:s", $final);

   $sql =" INSERT INTO POSTS (username, post_content, time_posted)
        VALUES ('$username','$content','$time_posted')";

    return $this->db->query($sql);
}

score 1 · Answer 3 · answered Mar 31 '17 at 18:42

1

MySQL time-stamp has a fixed pattern for date time input i.e. "Y-m-d H:i:s", If you want to insert date time into database then convert the pattern to the above mentioned one and then try. It'll work

answered Mar 31 '17 at 18:42

Vikash Mishra

349
3
18

Inserting DATETIME to SQL from PHP

3 Answers3