Export (0) Print
Expand All

How to: Retrieve Data as an Object

The SQL Server Driver for PHP provides the sqlsrv_fetch_object function for retrieving a row of data as a PHP object. This topic describes how to retrieve data when the optional $className parameter is used in the call to sqlsrv_fetch_object. For an example that demonstrates how to retrieve data as an object without using the optional $className parameter, see sqlsrv_fetch_object.

If a class name is specified with the optional $className parameter, an object of this class type is instantiated. If the properties of the class have names that match the result set field names, the corresponding result set values are applied to the properties. If a result set field name does not match a class property, a property with the result set field name is added to the object and the result set value is applied to the property.

The following rules apply when specifying a class with the $className parameter:

  • Matching is case-sensitive. For example, the property name CustomerId does not match the field name CustomerID. In this case, a CustomerID property would be added to the object and the value of the CustomerID field would be given to the CustomerID property.
  • Matching occurs regardless of access modifiers. For example, if the specified class has a private property whose name matches a result set field name, the value from the result set field is applied to the property.
  • Class property data types are ignored. If the "CustomerID" field in the result set is a string but the "CustomerID" property of the class is an integer, the string value from the result set is written to the "CustomerID" property.
  • If the specified class does not exist, the function returns false and adds an error to the error collection. For information about retrieving error information, see sqlsrv_errors.

The following example retrieves each row of a result set as an instance of the Product class defined in the script. The example retrieves product information from the Purchasing.PurchaseOrderDetail and Production.Product tables of the AdventureWorks database for products that have a specified due date (DueDate), and a stocked quantity (StockQty) less than a specified value. The example highlights some of the rules that apply when specifying a class in a call to sqlsrv_fetch_object:

  • The $product variable is an instance of the Product class, because "Product" was specified with the $className parameter and the Product class exists.
  • The Name property is added to the $product instance because the existing name property does not match.
  • The Color property is added to the $product instance because there is no matching property.
  • The private property UnitPrice is populated with the value of the UnitPrice field.

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

<?php
/* Define the Product class. */
class Product
{
     /* Constructor */
     public function Product($ID)
     {
          $this->objID = $ID;
     }
     public $objID;
     public $name;
     public $StockedQty;
     public $SafetyStockLevel;
     private $UnitPrice;
     function getPrice()
     {
          return $this->UnitPrice;
     }
}

/* Connect to the local server using Windows Authentication, and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Define the query. */
$tsql = "SELECT Name,
                SafetyStockLevel,
                StockedQty,
                UnitPrice,
                Color
         FROM Purchasing.PurchaseOrderDetail AS pdo
         JOIN Production.Product AS p
         ON pdo.ProductID = p.ProductID
         WHERE pdo.StockedQty < ?
         AND pdo.DueDate= ?";

/* Set the parameter values. */
$params = array(3, '2002-01-29');

/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql, $params);
if ( $stmt )
{
     echo "Statement executed.\n";
} 
else 
{
     echo "Error in statement execution.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Iterate through the result set, printing a row of data upon each
 iteration. Note the following:
     1) $product is an instance of the Product class.
     2) The $ctorParams parameter is required in the call to
        sqlsrv_fetch_object, because the Product class constructor is
        explicity defined and requires parameter values.
     3) The "Name" property is added to the $product instance because
        the existing "name" property does not match.
     4) The "Color" property is added to the $product instance
        because there is no matching property.
     5) The private property "UnitPrice" is populated with the value
        of the "UnitPrice" field.*/
$i=0; //Used as the $objID in the Product class constructor.
while( $product = sqlsrv_fetch_object( $stmt, "Product", array($i)))
{
     echo "Object ID: ".$product->objID."\n";
     echo "Product Name: ".$product->Name."\n";
     echo "Stocked Qty: ".$product->StockedQty."\n";
     echo "Safety Stock Level: ".$product->SafetyStockLevel."\n";
     echo "Product Color: ".$product->Color."\n";
     echo "Unit Price: ".$product->getPrice()."\n";
     echo "-----------------\n";
     $i++;
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

The sqlsrv_fetch_object function always returns data according to the Default PHP Data Types. For information about how to specify the PHP data type, see How to: Specify PHP Data Types or How to: Retrieve a Single Field.

If a field with no name is returned, sqlsrv_fetch_object will discard the field value and issue a warning. For example, consider this Transact-SQL statement that inserts a value into a database table and retrieves the server-generated primary key:

INSERT INTO Production.ProductPhoto (LargePhoto) VALUES (?);

SELECT SCOPE_IDENTITY()

If the results returned by this query are retrieved with sqlsrv_fetch_object, the value returned by SELECT SCOPE_IDENTITY() will be discarded and a warning will be issued. To avoid this, you can specify a name for the returned field in the Transact-SQL statement. The following is one way to specify a column name in Transact-SQL:

SELECT SCOPE_IDENTITY() AS PictureID

Community Additions

Show:
© 2014 Microsoft