Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 837
arun nair Avatar asked May 20 '11 18:05

arun nair


People also ask

How can I insert current date and time in MySQL using PHP?

The simplest method to insert the current date and time in MySQL is to use the now() function. Once you call the function, it returns the current date and time in the system's configured time zone as a string. The value returned from the now() function is YYYY-MM-DD for the date and HH-MM-SS-UU for the time record.

How do I insert the current date in timestamp?

Control + : (hold the control key and press the colon key). Here is how to use it: Select the cell where you want to insert the timestamp. This would instantly insert the current date in the cell.


5 Answers

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 . "'"; 
like image 86
Ash Burlaczenko Avatar answered Oct 04 '22 00:10

Ash Burlaczenko


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 . "'"; 
like image 32
Kalaivani M Avatar answered Oct 03 '22 22:10

Kalaivani M


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'";
like image 39
George Cummins Avatar answered Oct 03 '22 22:10

George Cummins


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

 $update_query = 
      "UPDATE db.tablename SET insert_time=NOW() WHERE username='" .$somename."'";
like image 44
Laurence Burke Avatar answered Oct 03 '22 22:10

Laurence Burke


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);
}
like image 39
redux Avatar answered Oct 03 '22 22:10

redux