can't figure out mysql datatime

Question

So I'm pulling my hair out over the most stupid of situations... I'm trying to save a new time to a MySQL DB and have tried everything I can think of to make this work, but I can't for some reason. Here's what I've got...

$_ = gmdate("Y-m-d H:i:s",time());
if (! mysql_query("UPDATE Accounts 
    SET likes='1',update='".$_."' 
    WHERE username='".$_POST['username']."'")) { ... }

I've tried:

update=FROM_UNIXTIME(".$_.")
update=UNIX_TIMESTAMP(".strtotime($_).")
update=UNIX_TIMESTAMP('".strtotime($_)."')
update='".UNIX_TIMESTAMP(strtotime($_))."'
update='".UNIX_TIMESTAMP($_)."'
update='".$_."'

Any help would greatly be appreciated! Thanks, Dave

I would advise always storing unix time in a database. You can always convert from it later in your application and it removes any hassles with DST or Timezones. Also, you should not use `mysql_*` functions anymore. — thatidiotguy, Jan 22 '13 at 14:33
fyi: your sample code is affectable by sql-injections! secure it before you use that code in production! — coding Bott, Jan 22 '13 at 14:37
@thatidiotguy Always storing dates generated using `gmdate()` does something similar :) — Ja͢ck, Jan 22 '13 at 14:43

score 1 · Accepted Answer · edited May 25 '19 at 14:35

If you're going to use update as a column name, you should escape it with backticks because it's a reserved word. The value of $_ is fine for the field itself.

mysql_query(" ... SET likes = 1, `update` = '$_' ...")

And another thing; you should escape the value of $_POST['username'] to avoid SQL injection:

" ... WHERE username='" . mysql_real_escape_string($_POST['username']) . "'");

You can also combine everything using a format string, like:

$sql = sprintf("UPDATE Accounts SET likes='%d',`update`=%s' WHERE username='%s'", 
    1,
    $_,
    mysql_real_escape_string($_POST['username'])
);

$res = mysql_query($sql) or die("yikes: " . mysql_error());

Also:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 0 · Answer 2 · edited May 25 '19 at 14:35

mysql_query("UPDATE Accounts SET likes='1',update='".$_."' WHERE username='".$_POST['username']."'")

You have two options, the now(), or curdate() functions within your query.

The now() returns the date, aswell as a time stamp in the format; YYYY-MM-DD HH:MM:SS

The curdate() returns the date in the format YYYY-MM-DD

Your query will look like:

mysql_query("UPDATE Accounts SET likes='1', `update`='now()' WHERE username='".$_POST['username']."'")

Notice the back ticks, this needs to be used on the update, because it's a reserved word. You need to escape the reservation so you can use it as a column name.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 0 · Answer 3 · answered Jan 22 '13 at 14:37

update is a keyword in MySQL syntax, you shouldn't name a field update. Try naming it something like: updated_at or updated_date

If you can't do this you must to quote the field name in your query:

"UPDATE Accounts 
SET likes='1',`update`='".$_."' 
WHERE username='".$_POST['username']."'"

can't figure out mysql datatime

3 Answers3