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
– arun nair, Commented May 20, 2011 at 18:08

Ash Burlaczenko · Accepted Answer · 2012-07-24 23:32:46Z

57

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, 2012 at 23:32

answered May 20, 2011 at 18:09

Ash Burlaczenko

25.6k16 gold badges70 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

arun nair Over a year ago

yes! that was the issue. guess i should sleep now. its late night here, IST. else these issues will creep all over the code!!

Nitin Bansal Over a year ago

NOW() is redundant, use UNIX_TIMESTAMP()

Ash Burlaczenko Over a year ago

@NitinBansal, can you provide a link the MySQL where it says is deprecated? I can't find any evidence to your claim.

Lee Fuller Over a year ago

@AshBurlaczenko He didn't say it was deprecated. He said it was redundant.

Ash Burlaczenko Over a year ago

@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.

|

Kalaivani M · Accepted Answer · 2016-10-12 07:05:16Z

20

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, 2016 at 7:05

Kalaivani M

1,31016 silver badges29 bronze badges

Comments

George Cummins · Accepted Answer · 2014-03-21 19:53:39Z

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, 2014 at 19:53

answered May 20, 2011 at 18:08

George Cummins

29k8 gold badges74 silver badges91 bronze badges

3 Comments

mjec Over a year ago

This is the solution, but you should ALWAYS escape variables before putting them into your database queries. Check out PDO 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.

George Cummins Over a year ago

@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.

Nitin Bansal Over a year ago

better: UNIX_TIMESTAMP()

Laurence Burke · Accepted Answer · 2011-05-20 18:07:36Z

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, 2011 at 18:07

Laurence Burke

2,36814 silver badges26 bronze badges

Comments

redux · Accepted Answer · 2013-12-03 05:05:55Z

1

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);
}

answered Dec 3, 2013 at 5:05

redux

1,16710 silver badges22 bronze badges

Comments

simhumileco · Accepted Answer · 2019-04-25 07:06:15Z

1

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

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

Difference between UNIX_TIMESTAMP and NOW() in MySQL

answered Apr 25, 2019 at 7:06

simhumileco

35.3k18 gold badges148 silver badges125 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

6 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related