MYSQL/PHP insert date

Question

hi guys can i insert this

date_default_timezone_set(Asia/Hong_Kong);
$date = date('"m/d/Y H:i:s"');

instead of

"INSERT INTO date_time(`submitted_time`) VALUES (NOW())"

because my problem is using NOW() or CURRENT_TIMESTAMP() gives me a inaccurate time

im doing this but when i look at the database this is what is been inserted 0000-00-00 00:00:00.0

date_default_timezone_set('Asia/Manila');

if(isset($_POST['submit'])) {
    $date = date('"m/d/Y H:i:s"');
        $query = "INSERT INTO `date_time`(`submitted_time`)
                    VALUES('$date')";
        if($conn->query($query)) {
            echo "success";
        } else {
            echo "error";
        }
}

yes when using NOW() or CURRENT_TIMESTAMP() it shows what its 7 hours late — JustAngelo, Feb 13 '17 at 11:22
Where is your server? Is it located in a timezone that is 7 hours late? — RiggsFolly, Feb 13 '17 at 11:22
thats what i write but when inserting to db its just shows `0000-00-00 00:00:00.0` — JustAngelo, Feb 13 '17 at 11:23
confirm that the date time is a timestamp/date field in your database — Rotimi, Feb 13 '17 at 11:24

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

If you actually want to insert a datetime that you generate this is how it is done.

NOTE: The format of a date must be Y-m-d H:i:s as that is the format MYSQL understands for its DateTime data type

date_default_timezone_set('Asia/Manila');

if(isset($_POST['submit'])) {

    $query = "INSERT INTO `date_time` (`submitted_time`) VALUES(?)";
    $stmt = $conn->prepare($query);

    // note the DateTime must be in this format to store correctly on a MYSQL database
    $dt = date('Y-m-d H:i:s');

    $stmt->bind_param('s', $dt);
    $status = $stmt->execute();

    if(!$status) {
        echo $stmt->error;    
        exit;
    }
    echo "success";
}

If you want to view, show your users a DateTime in any other format that is the job of the presentation layer to reformat it to whichever locale specific format.

Your script was at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

edited May 23 '17 at 10:29

Community

1
1

answered Feb 13 '17 at 11:27

RiggsFolly

93,638
21
103
149

getting this Parse error: syntax error, unexpected 'exit' (T_EXIT), expecting ',' or ';' – JustAngelo Feb 13 '17 at 11:33
Sorry little typo. Fixed preceeding line needed a `;` Like `echo $stmt->error; ` – RiggsFolly Feb 13 '17 at 11:34
Notice: Only variables should be passed by reference and this shows but it work btw. thanks a lot – JustAngelo Feb 13 '17 at 11:36
and may i ask, what syntax is this? is it PDO? – JustAngelo Feb 13 '17 at 11:37
No its `MYSQLI_` unless I made another typo – RiggsFolly Feb 13 '17 at 11:38
how can i remove the notice? – JustAngelo Feb 13 '17 at 11:39
`$status = $stmt->execute();` – JustAngelo Feb 13 '17 at 11:41
im sorry. its in this line ` $stmt->bind_param('s', date('Y-m-d H:i:s'));` – JustAngelo Feb 13 '17 at 11:46
Yea thats the line I changed – RiggsFolly Feb 13 '17 at 11:46
works perfectly can you please answer this so i can fully understand the code. how come the values is ? `VALUES(?)` – JustAngelo Feb 13 '17 at 11:49
Its all to do with protecting yourself from SQL Injection. See additional yellow note – RiggsFolly Feb 13 '17 at 11:55

MYSQL/PHP insert date

1 Answers1