Category Archives: MySQL

How To Assign The Current Date and Time to a MySQL Datetime Field using PHP

This is a no-brainer, but I always forget how to do it. MySQL datetime fields are stored in this format: 2010-02-18 10:33:18. Here’s a trivial example showing you how to populate a datetime field with the current date in the correct format, then write it to a table:

 // Assign current date to a MySQL date field:
 $eventdate = date('Y-m-d H:i:s');
 //... (mysql connection code goes here)
 mysql_query("INSERT INTO Events (Event, EventDate) VALUES ($eventtext, $eventdate)");

Another question might be: “Why?” – when MySQL has the now() function. I needed to do this in Drupal, because I’m using the drupal_write_record function to update a table in a custom module. This requires assigning PHP values to fields in my table, rather than using any built-in MySQL functions.

How To Format a MySQL Date For Output in PHP

The following PHP code parses the date portion out of a MySQL date column and lets you format it according to the formatting rules of the PHP date function.

// Parse date portion of a date field and format it according to the date() function.
// Second parm is optional. 
function FmtDateTime($dt, $fmt = "F d, Y")
{
	$arr = explode("-", substr($dt, 0, 10)); 
	//	$fmt of "m/d/Y" prints date in mm/dd/yyyy format. 
	//  $fmt of "F d, Y" prints date in Spelled out month day, YYYY format
	
	echo date($fmt, mktime(0,0,0, $arr[1], $arr[2], $arr[0])); 
}

To use it, you can do something like this:

<?php
   FmtDateTime($scdDate, "l d F, Y" ); ?> 

(This prints 2009-09-15 as “Tuesday 15 September, 2009”).

If you omit the second parameter on the function call, the date will print like this:
“September 15, 2009”

Forays into PHP and MySQL

I’ve just started working extensively with PHP and MySQL.  Here’s a simple tip that took me a while to figure out (despite copious examples on PHP.net, I couldn’t find this one:

My MySQL table has a  Timestamp (Z) field, used to assign the current date and time to it when a record is added. Using mySQL in PHP, I can assign the value to it like this:

$result=mysql_query(“INSERT INTO qNotices (nPostDateTime) VALUES(NOW()”);

or I can assign the value with a PHP assignment statement, using this:

  $nPostDateTime= date(‘YmdHis’);  // Same as NOW() MySQL function.
 $result=mysql_query(“INSERT INTO qNotices (nPostDateTime) VALUES($nPostDateTime”);

Note that you do not need any dashes or semi-colons in the format string here, even though the field looks like this :  2007-04-19 10:23:32 when you view the data with a SELECT statement.