How to insert the current date into a DATETIME field in a MySQL database using PHP
This is a simple but effective tip that I picked up this week.
In the past, if I wanted to insert the current date into a table in a MySQL database from within a PHP script, I would always do something like this:
<?php $current_date = date("Y-m-d H:i:s"); ... $query = "INSERT INTO guestbook SET date = '$current_date'"; $sql = mysql_query($query) or die(mysql_error()); ?>
Then it was pointed out to me, that this is in fact an arse-backwards way of doing things, as the variable
$current_date will enter MySQL as a string, which MySQL will have to parse back into a DATETIME type.
A much better / faster / cleaner way to do things is using CURRENT_TIMESTAMP, which is a built-in MySQL function
<?php $query = "INSERT INTO guestbook SET date = CURRENT_TIMESTAMP"; $sql = mysql_query($query) or die(mysql_error()); ?>
You can take this even further by constructing the date field in your database table thus:
date TIMESTAMP NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
Which means that it is automatically populated with the current date, the minute a record is created or updated.