How to insert the current timestamp into MySQL database using a PHP insert query

Question

In my MySQL database, I have a table with structure

username - varchar
insert_time - timestamp

This table was created in MySQL using the phpMyAdmin tool and for the insert_time column, I have mentioned default value as 0000-00-00 00:00:00.

Now the problem is, I have to update this default value with the current timestamp later on, using a PHP script.

I tried doing the following PHP code:

$update_query = 'UPDATE db.tablename SET insert_time=now() '.
                'WHERE username='.$somename;

When the PHP script is run, it fails, and is unable to insert anything into the database.

What am I doing wrong?

i've set a custom error to be displayed if the insert query fails (using a "or die()" in the query) — arun nair, May 20 '11 at 18:08

Ash Burlaczenko · Accepted Answer · 2012-07-24T23:32:46.197

56

What error message are you getting?

I'd guess your actual error is because your php variable isn't wrapped in quotes. Try this

$update_query = "UPDATE db.tablename SET insert_time=now() WHERE username='" .$somename . "'";

edited Jul 24 '12 at 23:32

answered May 20 '11 at 18:09

Ash Burlaczenko

5

yes! that was the issue. guess i should sleep now. its late night here, IST. else these issues will creep all over the code!! – arun nair May 20 '11 at 18:19
4

NOW() is redundant, use UNIX_TIMESTAMP() – Nitin Bansal Aug 30 '14 at 22:58
@NitinBansal, can you provide a link the MySQL where it says is deprecated? I can't find any evidence to your claim. – Ash Burlaczenko Aug 31 '14 at 09:54
@AshBurlaczenko He didn't say it was deprecated. He said it was redundant. – Lee Fuller Sep 22 '14 at 22:45
@LeeFuller, redundant means something will work without the other thing. NitinBansal is suggesting replacing now() not removing it so I assumed they'd got there words mixed up. – Ash Burlaczenko Sep 23 '14 at 09:28
I'm afraid that this SQL is vulnerable for SQL injections, so be careful. – Rafal Kozlowski Aug 12 '16 at 13:02

score 17 · Answer 2 · answered Oct 12 '16 at 07:05

17

This format is used to get current timestamp and stored in mysql

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

$update_query = "UPDATE db.tablename SET insert_time=".$date." WHERE username='" .$somename . "'";

answered Oct 12 '16 at 07:05

Kalaivani M

George Cummins · Answer 3 · 2014-03-21T19:53:39.393

15

Your usage of now() is correct. However, you need to use one type of quotes around the entire query and another around the values.

You can modify your query to use double quotes at the beginning and end, and single quotes around $somename:

$update_query = "UPDATE db.tablename SET insert_time=now() WHERE username='$somename'";

edited Mar 21 '14 at 19:53

answered May 20 '11 at 18:08

George Cummins

3

This is the solution, but you should ALWAYS escape variables before putting them into your database queries. Check out PDO http://php.net/pdo as a database abstraction layer which can help with this, or at least run mysql_real_escape_string($somename) before inserting it. – mjec May 20 '11 at 18:10
@mjec: You are correct, but I left it out to avoid clutter. The OP may be escaping the input before the line in question, or this may not be user input at all. – George Cummins May 20 '11 at 18:12
better: UNIX_TIMESTAMP() – Nitin Bansal Aug 30 '14 at 22:58

score 10 · Answer 4 · answered May 20 '11 at 18:07

10

Forgot to put the variable in the sql statement without quotations.

 $update_query = 
      "UPDATE db.tablename SET insert_time=NOW() WHERE username='" .$somename."'";

answered May 20 '11 at 18:07

Laurence Burke

score 1 · Answer 5 · answered Dec 03 '13 at 05:05

Don't like any of those solutions.

this is how i do it:

$update_query = "UPDATE db.tablename SET insert_time=now() WHERE username='" 
            . sqlEsc($somename) . "' ;";

then i use my own sqlEsc function:

function sqlEsc($val) 
{
    global $mysqli;
    return mysqli_real_escape_string($mysqli, $val);
}

score 1 · Answer 6 · answered Apr 25 '19 at 07:06

1

Instead of NOW() you can use UNIX_TIMESTAMP() also:

$update_query = "UPDATE db.tablename 
                 SET insert_time=UNIX_TIMESTAMP()
                 WHERE username='$somename'";

answered Apr 25 '19 at 07:06

simhumileco

6 Answers6