Practical Web Programming

Tuesday, December 02, 2008

PHP: Simple String to Date / Date to String Conversion Functions

In PHP even a simple INSERT and UPDATE to the database, MySQL specially, can lead to bugs in DATETIME fields. Whenever I do database insertion, I run into this kind of problem. Instead of the actual date, the string "0000-00-00 00:00:00" appears. This string represents NULL in date.

To combat this problem, I created simple String to Date / Date to String conversion functions. Before inserting/updating date to a MySQL database, convert first the date using text_to_date_format. Then, when querying, use the date_to_text_format to make the date more human readable.


//FORMAT DATE TO STRING
function date_to_string_format($date)
{
if (strtotime($date))
{
return date("M. d, Y h:i A", strtotime($date));
}
else
{
return "";
}
}

//FORMAT STRING TO DATE
function string_to_date_format($date)
{
if (strtotime($date))
{
return date("Y-m-d H:i:s", strtotime($date));
}
else
{
return "0000-00-00 00:00:00";
}
}


Here's what your INSERT SQL statement should look like using the above function.


$sql = "INSERT INTO my_table (
my_id,
my_date
) VALUES (
1, ".
string_to_date_format($var_date).")";

0 comments:

Recent Post