CODEWITHSUNDEEP
phpmysqltimestamp
arun nair
arun nair

Reputation: 3683

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

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?

Upvotes: 46

Views: 209725

Answers (6)

simhumileco
simhumileco

Reputation: 34515

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

Upvotes: 1

Kalaivani M
Kalaivani M

Reputation: 1290

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 . "'";

Upvotes: 19

George Cummins
George Cummins

Reputation: 28906

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'";

Upvotes: 15

redux
redux

Reputation: 1167

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

Upvotes: 1

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25435

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 . "'";

Upvotes: 57

Laurence Burke
Laurence Burke

Reputation: 2358

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

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

Upvotes: 10

Related Questions