Andrew Alderson Flash, Flex and Beyond

This is a problem that I have ran into in the past but never really investigated it. This problem has 2 parts - the first comes from sending a Date object from Flex through WebORB for PHP and then using that date in a sql query and the second comes from pulling a date from a MySQL table in PHP and returning it to Flex.

I ran into this problem again on an application I am currently developing and this time I stopped to investigate. It is a Flex application that uses a MySQL database and WebORB for PHP 3.5. This is a scheduling application and the database has multiple tables with columns of data type 'date' or 'time' or 'datetime'. I have a number of queries that require a date range for selecting items from the database. The problem I ran into was that the Date object sent from Flex through WebORB would not be excepted by MySQL as a valid date and the dates pulled from MySQL and sent back to Flex would not be serialized into a Date object in Flex

The solutions I tried are:
1) Sending the dates from Flex as a String and creating a date object in PHP to be used in the SQL query or simple cast the string to a date in the query. This worked fine except it didn't solve the second part of the problem. I could have just sent the date back as a string but this meant that the properties in the Value Object class would need to be of type String and then converted somewhere (or everywhere) in the application. This is the solution I used in the past but it is ugly.
2) Sending the dates as time. Not good because as I found out Flex stores time as the number of milliseconds that has elapsed since Jan. 1 1970 and PHP and MySQL store it as the number of seconds since Jan 1, 1970. When I was doing this I was wondering why there were so many more digits in the Flex date and because I am a Flex developer I kept blaming PHP and MySQL :). I could have simply divided the the time by 1000 before sending from Flex but again - ugly, and it still didn't solve the second problem.

I tried a few other solutions but none of them really worked and kept the code clean. I was striving for a clean solution where I would be able to send a Date from Flex and get a Date back in my Value Objects.

Say hello to the PHP DateTime Object. The solution was to make sure that the Date(s) sent from Flex were converted to a PHP DateTime object and any dates pulled from MySQL were also converted to a PHP DateTime object before sending them back to Flex so WebORB would serialize them correctly. You also have to make sure that the DateTime object is formatted correctly for use in a MySQL query. To make sure that the Date is serialized into a DateTime Object the solution is to make sure the parameter is typed as such.


public function getItemsForRange(DateTime $startDate, DateTime $endDate)
{
// this is the format for a MySQL 'date' datatype
// format is a method of DateTime
$start = $startDate->format("Y-m-d");
$end = $endDate->format("Y-m-d");

$sql = "SELECT * FROM {tablename} WHERE {date column} BETWEEN '$start' AND '$end';
$stmt = $this->db->prepare($sql);

$stmt->execute();

$row = $stmt->fetchObject();

// to send a date back
$myValueObject->dateField = new DateTime($row->date);

return $myValueObject;
}

In the above code the parameters are cast to a DateTime objects and then formatted correctly for the column data type in the MySQL table. If you need to send a date back to Flex that was pulled from MySQL just create a new DateTime object with the date pulled from the database as the parameter.

As a note - the above code uses PHP PDO (PHP Data Objects). I use this when I write all of my service classes that access a database. For more information on this just google `PHP PDO`.