In this article, I will review how to convert a variable that represents a date to an actual MySQL date column using PHP.

Dealing with dates and times in MySQL and PHP is at once fascinating and frustrating. Everybody seems to store their date information with slight variances. Thus, everyone’s solution is slightly different.

So, today I will show you how to take a date and time that are stored as text variables and convert them into dates to be placed in MySQL.

In the article Import CSV File Into MySQL, I illustrated how to import a .CSV file into MySQL. This import stored the date and time as standard VARCHAR variables that look like this: October 26, 2010 | 7:30 p.m.. Here, I will show you how to convert these to a proper DATE/TIME format and store them in the MySQL table. This process is actually very simple and can be done with just a few lines.

In the MySQL table, I created a new field called GAMEDATETIME and I marked this field with the datetime type.

I then created a php file which cycles through the table and gets the variables $gdate and $gtime, which again are VARCHAR variables that look like this: October 26, 2010 | 7:30 p.m.

So, the first thing we do (within the PHP loop) is create a combined date/time variable like this:
$dateandtime=$gdate.” “.$gtime;
This makes $dateandtime equal to a format like this: October 26, 2010 7:30p.m.

Next, we use the strtotime php feature to put this into a UNIX timestamp like this:
$newdatetime = strtotime( $dateandtime );
strtotime is able to parse and read our combined variable perfectly!

Finally, to get this date data ready to store in our MySQL database in a format that it likes, we execute the following line of code in our PHP file, which uses the PHP date function to properly configure the date and time:
$mydatetime = date(‘Y-m-d H:i:s’, $newdatetime );

Now, we update MySQL using the UPDATE command as follows:
$query1= “UPDATE sqltable SET GAMEDATETIME=’$mydatetime’ WHERE x= x”;
mysql_query($query1) or die(“Query failed: ” . mysql_error());

This updates the GAMEDATETIME column with a properly stored game date and time, which makes this information infinitely more valuable than if it were stored in a standard text string!

I hope this helps. Cheers,
Richard