How to: Create and insert SQL DATE or DATETIME objects using PHP

I was helping a student with a bit of project work the other day and had to create a properly formatted Date object from input provided by some dropdown menus, so in the spirit of only ever solving the same problem once, this is how you go about it… I’ll use MySQL for this example, but if the DATEs or DATETIMEs or whatever you’re using have a slightly different format in your DBMS of choice, then the principle’s the same to generate a compatible object.

MySQL Time Formats

The MySQL DATE type has the format YYYY-MM-DD, so for example today (or at least when I’m writing this post) would be 2011-08-19.

The MySQL DATETIME type has the format YYYY-MM-DD HH:ii:SS, so if it’s 9:36am and 53 seconds into the 19th of August 2011, that would be 2011-08-19 09:36:53.

The formatting of the numbers (i.e. the sequence of digits) is super important, but the dashes and colons are optional. This means that when you’re creating a PHP object to represent a DATE or DATETIME, you could create an object which contains 2011-08-19 or 20110819 etc. and MySQL would accept them as the exact same thing.

Constructing a suitable object in PHP

To create our PHP object, we’re going to use the PHP date and mktime functions. For this example I’m just going to create some variables which hold any date or time values, but in the real world you’d probably get them from a dropdown menu or calendar control.

	// Create variables to store 1:15pm and 28 seconds on the 20th of August 2011
	// NOTE: If you don't enclose your values with apostrophes or quotes, then things will go wrong because
	// single digit values need the leading zeroes, for example the 08 below will turn into 8, and then
	// the whole thing's broken. See the bottom of this post for an alternative padding method for numbers.
	$theYear   = '2011'; // YYYY
	$theMonth  = '08';   // MM
	$theDay    = '20';   // DD
	$theHour   = '13';   // HH
	$theMinute = '15';   // ii
	$theSecond = '28';   // SS
	// Create an object suitable for insertion into a MySQL DATE field
	// The date 'YmD' parameters mean: year as YYYY, month as MM w/ leading zeroes, day as DD w/ leading zeroes
	// Full PHP date parameters can be found at:
	// The format of mktime is: hour, minute, second, month, day, year
	// Full PHP mktime parameters can be found at:
	$sqlDate = date('Ymd', mktime(0, 0, 0, $theMonth, $theDay, $theYear));
	// Create an object suitable for insertion into a MySQL DATETIME field
	// The date 'YmdHis' means: Ymd as above, hours as HH w/ leading zeroes, minutes as ii w/ leading zeroes, ss as seconds w/ leading zeroes
	$sqlDateTime = date('YmdHis', mktime($theHour, $theMinute, $theSecond, $theMonth, $theDay, $theYear));
	// Now you've got your DATE or DATETIME object, sling 'em in your database with something like this...
	$dbConn = mysql_connect("localhost", "root", "") or die("It is a good day to die!: " . mysql_error()); // Default WAMP settings
	// Choose the database to work with
	mysql_select_db("MyLovelyDatabase", $dbConn) or die("It is a good day to fail to connect to your DB!: " . mysql_error());
	// Create the insert statement
	$sqlStatement = "INSERT INTO TestTable (someDate, someDateTime) VALUES ('$sqlDate', '$sqlDateTime')";
	// Execute the statement
	$result = mysql_query($sqlStatement, $dbConn);
	// Display result of the statement. If this is 1 then the insertion was successful, otherwise it wasn't
	echo "Statement executed and gave the following result: " . $result;
	// Shut down the connection to the database

Which when we then take a look at the database shows us:

DateTime Database Record


Automatically padding out numerical data with leading zeroes

If you need to work directly with numerical data (as opposed to strings), then you can use the following function (written by matrebatre over on the str_pad page) to pad out your data:

	function padNumber($theNumber, $numDigits)
		// str_pad format: value, desired number of characters, what to pad with, where to place the padding
		return str_pad((int) $theNumber, $numDigits, "0", STR_PAD_LEFT);
	$value = 5;
	$paddedValue = padNumber($value, 1); echo $paddedValue . "<br/>"; // 5
	$paddedValue = padNumber($value, 2); echo $paddedValue . "<br/>"; // 05
	$paddedValue = padNumber($value, 3); echo $paddedValue . "<br/>"; // 005

Done & dusted.

Site Change Pending – Welcome to 2011

I managed to break this site the other day when the theme I’m using (Freshy 2) decided to use a different set of options. I obviously triggered it somehow, but I’ve no idea how – and then it all got confused about which options and CSS adjunct to use, and didn’t want to work with the customize plugin and… yeah, it all went a bit Pete Tong. Given that the Freshy 2 theme is unsupported and doesn’t really work that well past WordPress 2.7 (which is pretty legacy), it’s no wonder strange things have been just waiting to occur, but I’ve got it back to a semblance on sanity (as you might be able to tell since you’re reading this, and noticing it doesn’t look a million miles different from before – apart from comment threading, promise me you won’t look!). I even backed up the entire www folder three days before it all went to hell, but when I restored the backup it’s still shafted, so the Freshy options must be stored elsewhere (like in the database somewhere)… but anyway, I digress; it’s time to shake things up.

Kicking and Screaming

I started reading about CSS3 the other day, and began knocking up examples of all the cool things you can do with opacity and 2D transitions and stuff – and it’s awesome. I had absolutely no idea you could do such cool stuff without JavaScript or Flash, and if you apply it to the experience layer (as opposed to the core functionality layer), then even if people are viewing your site in older browsers it can gracefully degrade and leave those running some archaic IE variant none the wiser that should they be seeing the site in Chrome or FF5 or such it’s got a lot more swish. I might even try some HTML5 shenanigans…

This isn’t going to change the core content for the better (sadly), but if the medium is truly the message, then perhaps by giving the medium an overhaul and a good lick of paint I can emphasize the message to a certain extent.

I don’t want to even think about how much time I could waste on 3D transitions… the sky’s the limit, ya know?

Stepping Up

So, to get the site all sorted, I have a (not especially cunning) plan:

  1. Get an offline version of the site running locally so I can twiddle the hell out of it. [COMPLETE]
  2. Rip the Freshy theme to bits, and combine all the CSS and CSS adjucts (which get pulled in and override the core styles) into a single CSS file. [COMPLETE – 2011-08-08]
  3. Remove all the cruft from the Freshy CSS and PHP. And there’s a lot of it. Tons. [96% COMPLETE 2011-08-10]
  4. Rewrite the core layout utilising CSS3 and perhaps HTML5 elements to make it look beautiful. [60% COMPLETE 2011-08-09]
  5. Bask in the radiant glory of a modern, dynamic website. [PENDING]
  6. Write good articles =P [ARGUABLE]

Speaking of which, I’ll write an article on getting an offline version of a site running soon – it actually took me a couple of hours to get everything installed, configured, database suitably modified (to keep me on the local copy at all times) & imported into MySQL etc. But now I have, I don’t have to do surgery on a live site, and can thus afford to break the hell out of my local copy, because things tend to get a lot worse before they get a lot better, and this version will still be up and running in 99% “good enough” mode.

Honestly though, CSS3 is amazing – check it out. I can’t wait to see what I can come up with =D