--- title: "Example Application (SQLSRV Driver) | Microsoft Docs" ms.custom: "" ms.date: "03/26/2018" ms.prod: sql ms.prod_service: connectivity ms.reviewer: "" ms.technology: connectivity ms.topic: conceptual helpviewer_keywords: - "example application" ms.assetid: c0225395-3a2e-4561-a2f2-8050ad11c8e2 author: David-Engel ms.author: v-daenge --- # Example Application (SQLSRV Driver) [!INCLUDE[Driver_PHP_Download](../../includes/driver_php_download.md)] The AdventureWorks Product Reviews example application is a Web application that uses the SQLSRV driver of [!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)]. The application lets a user search for products by entering a keyword, see reviews for a selected product, write a review for a selected product, and upload an image for a selected product. ### Running the Example Application 1. Install the [!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)]. For detailed information, see [Getting Started with the Microsoft Drivers for PHP for SQL Server](../../connect/php/getting-started-with-the-php-sql-driver.md). 2. Copy the code listed later in this document into two files: adventureworks_demo.php and photo.php. 3. Put the adventureworks_demo.php and photo.php files in the root directory of your Web server. 4. Run the application by starting `https://localhost/adventureworks_demo.php` from your browser. ## Requirements To run the AdventureWorks Product Reviews example application, the following must be true for your computer: - Your system meets the requirements for the [!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)]. For detailed information, see [System Requirements for the Microsoft Drivers for PHP for SQL Server](../../connect/php/system-requirements-for-the-php-sql-driver.md). - The adventureworks_demo.php and photo.php files are in the root directory of your Web server. The files must contain the code listed later in this document. - SQL Server 2005 or SQL Server 2008, with the [AdventureWorks2008](https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/adventure-works) database attached, is installed on the local computer. - A Web browser is installed. ## Demonstrates The AdventureWorks Product Reviews example application demonstrates the following: - How to open a connection to SQL Server by using Windows Authentication. - How to execute a parameterized query with [sqlsrv_query](../../connect/php/sqlsrv-query.md). - How to prepare and execute a parameterized query by using the combination of [sqlsrv_prepare](../../connect/php/sqlsrv-prepare.md) and [sqlsrv_execute](../../connect/php/sqlsrv-execute.md). - How to retrieve data by using [sqlsrv_fetch_array](../../connect/php/sqlsrv-fetch-array.md). - How to retrieve data by using the combination of [sqlsrv_fetch](../../connect/php/sqlsrv-fetch.md) and [sqlsrv_get_field](../../connect/php/sqlsrv-get-field.md). - How to retrieve data as a stream. - How to send data as a stream. - How to check for errors. ## Example The AdventureWorks Product Reviews example application returns product information from the database for products whose names contain a string entered by the user. From the list of returned products, the user can see reviews, see an image, upload an image, and write a review for a selected product. Put the following code in a file named adventureworks_demo.php: ```php AdventureWorks Product Reviews

AdventureWorks Product Reviews

This application is a demonstration of the procedural API (SQLSRV driver) of the Microsoft Drivers for PHP for SQL Server.

"AdventureWorks"); /* Connect using Windows Authentication. */ $conn = sqlsrv_connect( $serverName, $connectionOptions); if( $conn === false ) die( FormatErrors( sqlsrv_errors() ) ); if(isset($_REQUEST['action'])) { switch( $_REQUEST['action'] ) { /* Get AdventureWorks products by querying against the product name.*/ case 'getproducts': $params = array(&$_POST['query']); $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0"; /*Execute the query with a scrollable cursor so we can determine the number of rows returned.*/ $cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET); $getProducts = sqlsrv_query($conn, $tsql, $params, $cursorType); if ( $getProducts === false) die( FormatErrors( sqlsrv_errors() ) ); if(sqlsrv_has_rows($getProducts)) { $rowCount = sqlsrv_num_rows($getProducts); BeginProductsTable($rowCount); while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC)) { PopulateProductsTable( $row ); } EndProductsTable(); } else { DisplayNoProdutsMsg(); } GetSearchTerms( !null ); /* Free the statement and connection resources. */ sqlsrv_free_stmt( $getProducts ); sqlsrv_close( $conn ); break; /* Get reviews for a specified productID. */ case 'getreview': GetPicture( $_GET['productid'] ); GetReviews( $conn, $_GET['productid'] ); sqlsrv_close( $conn ); break; /* Write a review for a specified productID. */ case 'writereview': DisplayWriteReviewForm( $_POST['productid'] ); break; /* Submit a review to the database. */ case 'submitreview': /*Prepend the review so it can be opened as a stream.*/ $comments = "data://text/plain,".$_POST['comments']; $stream = fopen( $comments, "r" ); $tsql = "INSERT INTO Production.ProductReview (ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments) VALUES (?,?,?,?,?,?)"; $params = array(&$_POST['productid'], &$_POST['name'], date("Y-m-d"), &$_POST['email'], &$_POST['rating'], &$stream); /* Prepare and execute the statement. */ $insertReview = sqlsrv_prepare($conn, $tsql, $params); if( $insertReview === false ) die( FormatErrors( sqlsrv_errors() ) ); /* By default, all stream data is sent at the time of query execution. */ if( sqlsrv_execute($insertReview) === false ) die( FormatErrors( sqlsrv_errors() ) ); sqlsrv_free_stmt( $insertReview ); GetSearchTerms( true ); /* Display a list of reviews, including the latest addition. */ GetReviews( $conn, $_POST['productid'] ); sqlsrv_close( $conn ); break; /* Display a picture of the selected product.*/ case 'displaypicture': $tsql = "SELECT Name FROM Production.Product WHERE ProductID = ?"; $getName = sqlsrv_query($conn, $tsql, array(&$_GET['productid'])); if( $getName === false ) die( FormatErrors( sqlsrv_errors() ) ); if ( sqlsrv_fetch( $getName ) === false ) die( FormatErrors( sqlsrv_errors() ) ); $name = sqlsrv_get_field( $getName, 0); DisplayUploadPictureForm( $_GET['productid'], $name ); sqlsrv_close( $conn ); break; /* Upload a new picture for the selected product. */ case 'uploadpicture': $tsql = "INSERT INTO Production.ProductPhoto (LargePhoto) VALUES (?); SELECT SCOPE_IDENTITY() AS PhotoID"; $fileStream = fopen($_FILES['file']['tmp_name'], "r"); $uploadPic = sqlsrv_prepare($conn, $tsql, array( array(&$fileStream, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max')))); if( $uploadPic === false ) die( FormatErrors( sqlsrv_errors() ) ); if( sqlsrv_execute($uploadPic) === false ) die( FormatErrors( sqlsrv_errors() ) ); /*Skip the open result set (row affected). */ $next_result = sqlsrv_next_result($uploadPic); if( $next_result === false ) die( FormatErrors( sqlsrv_errors() ) ); /* Fetch the next result set. */ if( sqlsrv_fetch($uploadPic) === false) die( FormatErrors( sqlsrv_errors() ) ); /* Get the first field - the identity from INSERT. */ $photoID = sqlsrv_get_field($uploadPic, 0); /* Associate the new photoID with the productID. */ $tsql = "UPDATE Production.ProductProductPhoto SET ProductPhotoID = ? WHERE ProductID = ?"; $reslt = sqlsrv_query($conn, $tsql, array(&$photoID, &$_POST['productid'])); if($reslt === false ) die( FormatErrors( sqlsrv_errors() ) ); GetPicture( $_POST['productid']); DisplayWriteReviewButton( $_POST['productid'] ); GetSearchTerms (!null); sqlsrv_close( $conn ); break; }//End Switch } else { GetSearchTerms( !null ); } function GetPicture( $productID ) { echo ""; echo ""; echo "
"; echo "
Upload new picture.

"; } function GetReviews( $conn, $productID ) { $tsql = "SELECT ReviewerName, CONVERT(varchar(32), ReviewDate, 107) AS [ReviewDate], Rating, Comments FROM Production.ProductReview WHERE ProductID = ? ORDER BY ReviewDate DESC"; /*Execute the query with a scrollable cursor so we can determine the number of rows returned.*/ $cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET); $getReviews = sqlsrv_query( $conn, $tsql, array(&$productID), $cursorType); if( $getReviews === false ) die( FormatErrors( sqlsrv_errors() ) ); if(sqlsrv_has_rows($getReviews)) { $rowCount = sqlsrv_num_rows($getReviews); echo ""; echo "
$rowCount Reviews
"; while ( sqlsrv_fetch( $getReviews ) ) { $name = sqlsrv_get_field( $getReviews, 0 ); $date = sqlsrv_get_field( $getReviews, 1 ); $rating = sqlsrv_get_field( $getReviews, 2 ); /* Open comments as a stream. */ $comments = sqlsrv_get_field( $getReviews, 3, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR)); DisplayReview($productID, $name, $date, $rating, $comments ); } } else { DisplayNoReviewsMsg(); } DisplayWriteReviewButton( $productID ); sqlsrv_free_stmt( $getReviews ); } /*** Presentation and Utility Functions ***/ function BeginProductsTable($rowCount) { /* Display the beginning of the search results table. */ $headings = array("Product ID", "Product Name", "Color", "Size", "Price"); echo ""; echo "$rowCount Results"; foreach ( $headings as $heading ) { echo ""; } echo ""; } function DisplayNoProdutsMsg() { echo "

No products found.

"; } function DisplayNoReviewsMsg() { echo "

There are no reviews for this product.

"; } function DisplayReview( $productID, $name, $date, $rating, $comments) { /* Display a product review. */ echo "
$heading
"; echo ""; echo "
ProductID Reviewer Date Rating
$productID $name $date $rating
"; fpassthru( $comments ); echo "


"; } function DisplayUploadPictureForm( $productID, $name ) { echo "

Upload Picture

"; echo "

$name

"; echo "
"; } function DisplayWriteReviewButton( $productID ) { echo "

"; } function DisplayWriteReviewForm( $productID ) { /* Display the form for entering a product review. */ echo "
Name, E-mail, and Rating are required fields.
"; echo "
Name:
E-mail:
Rating: 1 2 3 4 5

"; } function EndProductsTable() { echo "
"; } function GetSearchTerms( $success ) { /* Get and submit terms for searching the database. */ if (is_null( $success )) { echo "

Review successfully submitted.

";} echo "

Enter search terms to find products.

"; echo "
"; } function PopulateProductsTable( $values ) { /* Populate Products table with search results. */ $productID = $values['ProductID']; echo ""; foreach ( $values as $key => $value ) { if ( 0 == strcasecmp( "Name", $key ) ) { echo "$value"; } elseif( !is_null( $value ) ) { if ( 0 == strcasecmp( "ListPrice", $key ) ) { /* Format with two digits of precision. */ $formattedPrice = sprintf("%.2f", $value); echo "$$formattedPrice"; } else { echo "$value"; } } else { echo "N/A"; } } echo "
"; } function FormatErrors( $errors ) { /* Display errors. */ echo "Error information:
"; foreach ( $errors as $error ) { echo "SQLSTATE: ".$error['SQLSTATE']."
"; echo "Code: ".$error['code']."
"; echo "Message: ".$error['message']."
"; } } ?> ``` ## Example The photo.php script returns a product photo for a specified **ProductID**. This script is called from the adventureworks_demo.php script. Put the following code in a file named photo.php: ```php "AdventureWorks"); /* Connect using Windows Authentication. */ $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn === false ) { echo "Could not connect.\n"; die( print_r( sqlsrv_errors(), true)); } /* Get the product picture for a given product ID. */ $tsql = "SELECT LargePhoto FROM Production.ProductPhoto AS p JOIN Production.ProductProductPhoto AS q ON p.ProductPhotoID = q.ProductPhotoID WHERE ProductID = ?"; $params = array(&$_REQUEST['productId']); /* Execute the query. */ $stmt = sqlsrv_query($conn, $tsql, $params); if( $stmt === false ) { echo "Error in statement execution.
"; die( print_r( sqlsrv_errors(), true)); } /* Retrieve the image as a binary stream. */ $getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY); if ( sqlsrv_fetch( $stmt ) ) { $image = sqlsrv_get_field( $stmt, 0, $getAsType); fpassthru($image); } else { echo "Error in retrieving data.
"; die(print_r( sqlsrv_errors(), true)); } /* Free the statement and connection resources. */ sqlsrv_free_stmt( $stmt ); sqlsrv_close( $conn ); ?> ``` ## See Also [Connecting to the Server](../../connect/php/connecting-to-the-server.md) [Comparing Execution Functions](../../connect/php/comparing-execution-functions.md) [Retrieving Data](../../connect/php/retrieving-data.md) [Updating Data (Microsoft Drivers for PHP for SQL Server)](../../connect/php/updating-data-microsoft-drivers-for-php-for-sql-server.md) [SQLSRV Driver API Reference](../../connect/php/sqlsrv-driver-api-reference.md)