How to save date to database?

Question

I have a database column called "worked", it is set to DATETIME type. I want this to hold the time that is inserted into it.

I get the date like this

$date = date('Y-m-d H:i:s');

I then echo it to check it like this

echo $date,"</br>";

Which displays

2014-10-21 21:35:12

So that works, but I now want to save that $date into the "worked" column, like this

$ins2 = mysqli_query($con,"UPDATE stats SET stats.worked = $date WHERE stats.id=$id2");

id2 is the ID of the user logged in and it is correct.

The problem is that it doesn't do anything!

Nothing happens, the field stays as "0000-00-00 00:00:00"

Try enclosing $date in quotes. Also parameterize your queries... always. — stakolee, Oct 21 '14 at 19:40
@Stakolee quoting it worked, I thought quotes were for strings only! — Ryan Mckenna, Oct 21 '14 at 19:43
Read and follow: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php - it will also likely "fix" this problem. — user2864740, Oct 21 '14 at 19:45
@RyanMckenna *"I thought quotes were for strings only!"* - Strings yes, but not when strings include `-` and `:` as in `2014-10-21 21:35:12` which SQL would interpret as 2014 minus 10 minus 21 etc. resulting in an error. Spaces included. — Funk Forty Niner, Oct 21 '14 at 19:45
Having used `or die(mysqli_error($con))` to `mysqli_query()` would have signaled the error. — Funk Forty Niner, Oct 21 '14 at 19:48
@Fred-ii- True lol I will start adding that "or die" after every query. — Ryan Mckenna, Oct 21 '14 at 19:48
This one should serve a purpose for someone else, somewhere down the line, *am sure*. The web is a small world ;) — Funk Forty Niner, Oct 21 '14 at 19:52
@RyanMckenna you could simplify it by using MySQL date and time functions like this: `UPDATE stats SET stats.worked = NOW() WHERE stats.id=$id2` as you can see I replaced `$date` with `NOW()` which will give you the current date and time just like the PHP function does. — Prix, Oct 21 '14 at 19:59

score 0 · Answer 1 · answered Mar 20 '20 at 00:19

It didn't work for you, because you didn't enclose the date in quotes. Dates are string literals and every string literal in SQL must be enclosed in quotes.

This is not the right way to pass the data to SQL though. You should pass the data via parameters.

Using mysqli it would look like this:

$ins2 = $con->prepare('UPDATE stats SET stats.worked = ? WHERE stats.id=?');
$ins2->bind_param('ss', $date, $id2);
$ins2->execute();

However, I strongly recommend to use PDO instead of mysqli. It is much simpler. The same code in PDO is one line only.

$pdo->prepare('UPDATE stats SET stats.worked = ? WHERE stats.id=?')->execute([$date, $id2]);

How to save date to database?

1 Answers1