PHP and SQL Server Datetime Objects

Description:

  • PHP bug, see https://bugs.php.net/bug.php?id=49382
  • An array containing datetime objects is returned from sql server
  • The objects are not available until  you run print_r ,var_dump or get_object_vars

For this example, I have a table that with two columns

Column1Name varchar(100)
Column2Name datetime

The problem occurs when you try to extract the value from Column2Name

  • To reproduce the problem, do this:
    • install PHP Version 5.4 or below (this may be fixed in later versions)
    • From PHP, run a sqlsrv_query that selects the data into an associative array where the table column names are the key values of the array.
    • When you try to read the datetime object, you will not succeed (unless you run get_object_vars first)

Notice the DateTimeObjects that are created.  These objects where not available in the normal way with PHP,until after I ran print_r, var_dump or get_object_vars

Listing 1:

  • Array( [0] => Array ( [Column1Name] => asdf  [Column2Name] => DateTime Object ( [date] => 2015-01-03 00:00:00 [timezone_type] => 3 [timezone] => UTC )))

Solution:

  • Fetch each array element and test for the variable type
  • if the type is an object, run get_object_vars($value) on the object, where $value is the object.
  • After get_object_vars(), you can read the values as elements of an associative array

e.g.,  $stmt = ‘select * from OurTable ‘;

$userData = sqlsrv_query($conn,$stmt, array(), array("Scrollable"=>"buffered"));
$arrKeys = array_keys($theResult["0"]);
while( $theResult = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) 
{
foreach($theResult[0] as $value) {
    if (is_object($value))
    {
        $arr = get_object_vars($value);
        echo $arr[date].","
        echo .$arr[timezone_type].","
        echo $arr[timezone]."<br>";

        /*
        this output shows something like the following:
    
        2015-01-03 00:00:00 , 3 , UTC
   
        */

    }
    if (is_numeric($value)) {
    …. }
    if (is_null($value)) {
    …. }
    if (is_null($value)) {
    …. }
}
  • In this example,
    • $theResult is the array that is returned in Listing 1.
    • $arrkeys is an array that contains the names of the keys in $theResult
  • For example,
    • $theReturn[0][$arrKeys[31]]  would be equivalent to $theReturn[0][‘submitdate’]  , which in this example is the DateTimeObject that has attributes of date, timezone_type, and timezone.