---
title: "Example Application (PDO_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
ms.assetid: a153e4ce-992d-4211-9a0f-c0998c706402
author: David-Engel
ms.author: v-daenge
---
# Example Application (PDO_SQLSRV Driver)
[!INCLUDE[Driver_PHP_Download](../../includes/driver_php_download.md)]
The AdventureWorks Product Reviews example application is a Web application that uses the PDO_SQLSRV driver of the [!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 prepare and execute a parameterized query.
- How to retrieve data.
- 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_pdo.php:
```
AdventureWorks Product Reviews
AdventureWorks Product Reviews
This application is a demonstration of the
object oriented API (PDO_SQLSRV driver) for the
Microsoft Drivers for PHP for SQL Server.
setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
if(isset($_REQUEST['action']))
{
switch( $_REQUEST['action'] )
{
/* Get AdventureWorks products by querying against the product name.*/
case 'getproducts':
try
{
$params = array($_POST['query']);
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice
FROM Production.Product
WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0";
$getProducts = $conn->prepare($tsql);
$getProducts->execute($params);
$products = $getProducts->fetchAll(PDO::FETCH_ASSOC);
$productCount = count($products);
if($productCount > 0)
{
BeginProductsTable($productCount);
foreach( $products as $row )
{
PopulateProductsTable( $row );
}
EndProductsTable();
}
else
{
DisplayNoProdutsMsg();
}
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
GetSearchTerms( !null );
break;
/* Get reviews for a specified productID. */
case 'getreview':
GetPicture( $_GET['productid'] );
GetReviews( $conn, $_GET['productid'] );
break;
/* Write a review for a specified productID. */
case 'writereview':
DisplayWriteReviewForm( $_POST['productid'] );
break;
/* Submit a review to the database. */
case 'submitreview':
try
{
$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'],
&$_POST['comments']);
$insertReview = $conn->prepare($tsql);
$insertReview->execute($params);
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
GetSearchTerms( true );
GetReviews( $conn, $_POST['productid'] );
break;
/* Display form for uploading a picture.*/
case 'displayuploadpictureform':
try
{
$tsql = "SELECT Name FROM Production.Product WHERE ProductID = ?";
$getName = $conn->prepare($tsql);
$getName->execute(array($_GET['productid']));
$name = $getName->fetchColumn(0);
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
DisplayUploadPictureForm( $_GET['productid'], $name );
break;
/* Upload a new picture for the selected product. */
case 'uploadpicture':
try
{
$tsql = "INSERT INTO Production.ProductPhoto (LargePhoto)
VALUES (?)";
$uploadPic = $conn->prepare($tsql);
$fileStream = fopen($_FILES['file']['tmp_name'], "r");
$uploadPic->bindParam(1,
$fileStream,
PDO::PARAM_LOB,
0,
PDO::SQLSRV_ENCODING_BINARY);
$uploadPic->execute();
/* Get the first field - the identity from INSERT -
so we can associate it with the product ID. */
$photoID = $conn->lastInsertId();
$tsql = "UPDATE Production.ProductProductPhoto
SET ProductPhotoID = ?
WHERE ProductID = ?";
$associateIds = $conn->prepare($tsql);
$associateIds->execute(array($photoID, $_POST['productid']));
}
catch(Exception $e)
{
die(print_r($e->getMessage()));
}
GetPicture( $_POST['productid']);
DisplayWriteReviewButton( $_POST['productid'] );
GetSearchTerms (!null);
break;
}//End Switch
}
else
{
GetSearchTerms( !null );
}
function GetPicture( $productID )
{
echo "";
}
function GetReviews( $conn, $productID )
{
try
{
$tsql = "SELECT ReviewerName,
CONVERT(varchar(32),
ReviewDate, 107) AS [ReviewDate],
Rating,
Comments
FROM Production.ProductReview
WHERE ProductID = ?
ORDER BY ReviewDate DESC";
$getReviews = $conn->prepare( $tsql);
$getReviews->execute(array($productID));
$reviews = $getReviews->fetchAll(PDO::FETCH_NUM);
$reviewCount = count($reviews);
if($reviewCount > 0 )
{
foreach($reviews as $row)
{
$name = $row[0];
$date = $row[1];
$rating = $row[2];
$comments = $row[3];
DisplayReview( $productID, $name, $date, $rating, $comments );
}
}
else
{
DisplayNoReviewsMsg();
}
}
catch(Exception $e)
{
die(print_r($e->getMessage()));
}
DisplayWriteReviewButton( $productID );
GetSearchTerms(!null);
}
/*** 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 "| $heading | ";
}
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 "";
echo "
| ProductID |
Reviewer |
Date |
Rating |
";
echo "
| $productID |
$name |
$date |
$rating |
| $comments |
";
}
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 "";
}
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 "
|
";
}
?>
```
## 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_pdo.php:
```
setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
/* Get the product picture for a given product ID. */
try
{
$tsql = "SELECT LargePhoto
FROM Production.ProductPhoto AS p
JOIN Production.ProductProductPhoto AS q
ON p.ProductPhotoID = q.ProductPhotoID
WHERE ProductID = ?";
$stmt = $conn->prepare($tsql);
$stmt->execute(array(&$_GET['productId']));
$stmt->bindColumn(1, $image, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
echo $image;
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
?>
```
## 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)