Datetime field - MySQL and PHP

Question

How can I insert the current date into a datetime field in MySQL database?

I have the code to connect to the database, make a query, and execute the query in php (which I've done plenty of times), but I've never dealt with dates in SQL or in PHP. Here's what I tried, but it's just returning 0 for the time:

<?php
$con = mysql_connect("somewebsite.com","user","somepassword");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("someuser", $con);

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );

$query = "insert into sometable
            (field1, field2, field3, date) 
          values 
            ('" . $_GET['name'] . "', '" . $_GET['pswd'] . "', '0', " . $phpdate . ")";
echo $query;
$result = mysql_query($query);

mysql_close($con);
?>

Not related to your question, but passing $_GET parameters straight into your query leaves you wide open to SQL injection. You should sanitize all inputs. For info on SQL injection, see: en.wikipedia.org/wiki/SQL_injection There's also lots of questions on SO related to SQL injection prevention, so you can do a search on here for examples too. — Rich Adams
– Rich Adams, Commented Jul 12, 2011 at 18:49
Do a Google search for "Prepared Statements in PHP" (Here's a practical example from PHP Docs: php.net/manual/en/pdo.prepared-statements.php#example-920) , as that would be the best way. But at a very basic level, you can do mysql_real_escape_string($_GET['name']) instead of just $_GET['name']. You should also verify the inputs are as you expect. If you're expecting a numeric value, confirm the variable contains a numeric value, etc. — Rich Adams
– Rich Adams, Commented Jul 12, 2011 at 18:53

Naftali · Accepted Answer · 2011-07-12 18:46:51Z

4

$query = "insert into sometable (field1, field2, field3, date) 
           values ('" . $_GET['name'] . "', '" . $_GET['pswd'] . "', 
           '0', CURRENT_TIMESTAMP)";

CURRENT_TIMESTAMP is the key, MySQL does it all for you.

answered Jul 12, 2011 at 18:46

Naftali

147k41 gold badges247 silver badges304 bronze badges

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

15 Comments

OMG Ponies Over a year ago

sql injection attack waiting to happen

CodeGuy Over a year ago

thanks that worked well. but, how do I make it so that it's eastern time zone?

Naftali Over a year ago

@OMGPonies that is true, i was just referring to the timestamp bit.

Marc B Over a year ago

There's also the now() function, which does the same thing.

Naftali Over a year ago

@reising it will go with whatever timezone your server is set up for.

|

Barry Chapman · Accepted Answer · 2011-07-12 18:46:46Z

0

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

or, for $phptime

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

answered Jul 12, 2011 at 18:46

Barry Chapman

6,7904 gold badges40 silver badges71 bronze badges

4 Comments

Marc B Over a year ago

What happens if the PHP executes at 11:59:59pm and the query doesn't take place until 00:00:01am the next day? Best to let MySQL do the timestamping to keep things consistent.

Barry Chapman Over a year ago

What if he is doing it from a value he previously had stored? He will be inserting the value he performed the operation on.

Barry Chapman Over a year ago

If he wants to use the current time, he could just use NOW().

Barry Chapman Over a year ago

Also, be sure to make use of mysql_real_escape_string() to prevent SQL injection.

Collectives™ on Stack Overflow

Datetime field - MySQL and PHP

2 Answers 2

15 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related