예시 애플리케이션(SQLSRV 드라이버)
AdventureWorks 제품 검토 예시 애플리케이션은 Microsoft Drivers for PHP for SQL Server의 SQLSRV 드라이버를 사용하는 웹 애플리케이션입니다. 애플리케이션을 사용하면 사용자가 키워드를 입력하고, 선택한 제품에 대한 리뷰를 살펴보며, 선택한 제품에 대한 검토를 작성하고, 선택한 제품에 대한 이미지를 업로드하여 제품을 검색할 수 있습니다.
예시 애플리케이션 실행
- Microsoft Drivers for PHP for SQL Server 설치 자세한 내용은 SQL Server용 Microsoft Drivers for PHP 시작을 참조하세요.
- 이 문서의 뒷부분에 나열된 코드를 adventureworks_demo.php 및 photo.php 두 파일에 복사합니다.
- adventureworks_demo.php 및 photo.php 파일을 웹 서버의 루트 디렉터리에 배치합니다.
- 브라우저에서
https://localhost/adventureworks_demo.php를 시작하여 애플리케이션을 실행합니다.
요구 사항
AdventureWorks 제품 검토 예시 애플리케이션을 실행하려면 해당 컴퓨터에 대해 다음 조건을 만족해야 합니다.
- 시스템이 Microsoft Drivers for PHP for SQL Server의 요구 사항을 충족합니다. 자세한 내용은 Microsoft Drivers for PHP for SQL Server에 대한 시스템 요구 사항을 참조하세요.
- adventureworks_demo.php 및 photo.php 파일이 웹 서버의 루트 디렉터리에 있어야 합니다. 파일에는 이 문서의 뒷부분에 나열된 코드가 포함되어야 합니다.
- AdventureWorks2008 데이터베이스가 연결된 SQL Server 2005 또는 SQL Server 2008이 로컬 컴퓨터에 설치됩니다.
- 웹 브라우저가 설치됩니다.
AdventureWorks 예시
AdventureWorks 제품 검토 예시 애플리케이션은 다음을 보여줍니다.
- Windows 인증을 사용하여 SQL Server에 대한 연결을 여는 방법
- sqlsrv_query를 사용하여 매개 변수가 있는 쿼리를 실행하는 방법
- sqlsrv_prepare 및 sqlsrv_execute의 조합을 사용하여 매개 변수가 있는 쿼리를 준비하고 실행하는 방법.
- sqlsrv_fetch_array를 사용하여 데이터를 검색하는 방법
- sqlsrv_fetch 및 sqlsrv_get_field의 조합을 사용하여 데이터를 검색하는 방법.
- 데이터를 스트림으로 검색하는 방법.
- 방법: 데이터를 스트림으로 전송.
- 오류 확인 방법.
다음은 이름에 사용자가 입력한 문자열이 포함된 제품의 제품 정보를 데이터베이스에서 반환하는 예시입니다. 반환된 제품 목록에서 사용자는 리뷰를 보고, 이미지를 살펴보며, 이미지를 업로드하고, 선택한 제품에 대한 리뷰를 작성할 수 있습니다.
adventureworks_demo.php 파일에 다음 코드를 추가합니다.
<!--=============
This file is part of a Microsoft SQL Server Shared Source Application.
Copyright (C) Microsoft Corporation. All rights reserved.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============= *-->
<!--Note: The presentation formatting of the example application -->
<!-- is intentionally simple to emphasize the SQL Server -->
<!-- data access code.-->
<html>
<head>
<title>AdventureWorks Product Reviews</title>
</head>
<body>
<h1 align='center'>AdventureWorks Product Reviews</h1>
<h5 align='center'>This application is a demonstration of the
procedural API (SQLSRV driver) of the Microsoft
Drivers for PHP for SQL Server.</h5><br/>
<?php
$serverName = "(local)\sqlexpress";
$connectionOptions = array("Database"=>"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
{
DisplayNoProductsMsg();
}
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 "<table align='center'><tr align='center'><td>";
echo "<img src='photo.php?productId=".$productID."'
height='150' width='150'/></td></tr>";
echo "<tr align='center'><td><a href='?action=displaypicture&
productid=".$productID."'>Upload new picture.</a></td></tr>";
echo "</td></tr></table></br>";
}
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 "<table width='50%' align='center' border='1px'>";
echo "<tr bgcolor='silver'><td>$rowCount Reviews</td></tr></table>";
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 "<table align='center' cellpadding='5'>";
echo "<tr bgcolor='silver'>$rowCount Results</tr><tr>";
foreach ( $headings as $heading )
{
echo "<td>$heading</td>";
}
echo "</tr>";
}
function DisplayNoProductsMsg()
{
echo "<h4 align='center'>No products found.</h4>";
}
function DisplayNoReviewsMsg()
{
echo "<h4 align='center'>There are no reviews for this product.</h4>";
}
function DisplayReview( $productID, $name, $date, $rating, $comments)
{
/* Display a product review. */
echo "<table style='WORD-BREAK:BREAK-ALL' width='50%'
align='center' border='1' cellpadding='5'>";
echo "<tr>
<td>ProductID</td>
<td>Reviewer</td>
<td>Date</td>
<td>Rating</td>
</tr>";
echo "<tr>
<td>$productID</td>
<td>$name</td>
<td>$date</td>
<td>$rating</td>
</tr>
<tr>
<td width='50%' colspan='4'>";
fpassthru( $comments );
echo "</td></tr></table><br/><br/>";
}
function DisplayUploadPictureForm( $productID, $name )
{
echo "<h3 align='center'>Upload Picture</h3>";
echo "<h4 align='center'>$name</h4>";
echo "<form align='center' action='adventureworks_demo.php'
enctype='multipart/form-data' method='POST'>
<input type='hidden' name='action' value='uploadpicture'/>
<input type='hidden' name='productid' value='$productID'/>
<table align='center'>
<tr>
<td align='center'>
<input id='fileName' type='file' name='file'/>
</td>
</tr>
<tr>
<td align='center'>
<input type='submit' name='submit' value='Upload Picture'/>
</td>
</tr>
</table>
</form>";
}
function DisplayWriteReviewButton( $productID )
{
echo "<table align='center'><form action='adventureworks_demo.php'
enctype='multipart/form-data' method='POST'>
<input type='hidden' name='action' value='writereview'/>
<input type='hidden' name='productid' value='$productID'/>
<input type='submit' name='submit' value='Write a Review'/>
</p></td></tr></form></table>";
}
function DisplayWriteReviewForm( $productID )
{
/* Display the form for entering a product review. */
echo "<h5 align='center'>Name, E-mail, and Rating are required fields.</h5>";
echo "<table align='center'>
<form action='adventureworks_demo.php'
enctype='multipart/form-data' method='POST'>
<input type='hidden' name='action' value='submitreview'/>
<input type='hidden' name='productid' value='$productID'/>
<tr>
<td colspan='5'>Name: <input type='text' name='name' size='50'/></td>
</tr>
<tr>
<td colspan='5'>E-mail: <input type='text' name='email' size='50'/></td>
</tr>
<tr>
<td>Rating: 1<input type='radio' name='rating' value='1'/></td>
<td>2<input type='radio' name='rating' value='2'/></td>
<td>3<input type='radio' name='rating' value='3'/></td>
<td>4<input type='radio' name='rating' value='4'/></td>
<td>5<input type='radio' name='rating' value='5'/></td>
</tr>
<tr>
<td colspan='5'>
<textarea rows='20' cols ='50' name='comments'>[Write comments here.]</textarea>
</td>
</tr>
<tr>
<td colspan='5'>
<p align='center'><input type='submit' name='submit' value='Submit Review'/>
</td>
</tr>
</form>
</table>";
}
function EndProductsTable()
{
echo "</table><br/>";
}
function GetSearchTerms( $success )
{
/* Get and submit terms for searching the database. */
if (is_null( $success ))
{
echo "<h4 align='center'>Review successfully submitted.</h4>";}
echo "<h4 align='center'>Enter search terms to find products.</h4>";
echo "<table align='center'>
<form action='adventureworks_demo.php'
enctype='multipart/form-data' method='POST'>
<input type='hidden' name='action' value='getproducts'/>
<tr>
<td><input type='text' name='query' size='40'/></td>
</tr>
<tr align='center'>
<td><input type='submit' name='submit' value='Search'/></td>
</tr>
</form>
</table>";
}
function PopulateProductsTable( $values )
{
/* Populate Products table with search results. */
$productID = $values['ProductID'];
echo "<tr>";
foreach ( $values as $key => $value )
{
if ( 0 == strcasecmp( "Name", $key ) )
{
echo "<td><a href='?action=getreview&productid=$productID'>$value</a></td>";
}
elseif( !is_null( $value ) )
{
if ( 0 == strcasecmp( "ListPrice", $key ) )
{
/* Format with two digits of precision. */
$formattedPrice = sprintf("%.2f", $value);
echo "<td>$$formattedPrice</td>";
}
else
{
echo "<td>$value</td>";
}
}
else
{
echo "<td>N/A</td>";
}
}
echo "<td>
<form action='adventureworks_demo.php'
enctype='multipart/form-data' method='POST'>
<input type='hidden' name='action' value='writereview'/>
<input type='hidden' name='productid' value='$productID'/>
<input type='submit' name='submit' value='Write a Review'/>
</td></tr>
</form></td></tr>";
}
function FormatErrors( $errors )
{
/* Display errors. */
echo "Error information: <br/>";
foreach ( $errors as $error )
{
echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";
echo "Code: ".$error['code']."<br/>";
echo "Message: ".$error['message']."<br/>";
}
}
?>
</body>
</html>
LOB 예시
photo.php 스크립트는 지정된 ProductID에 대한 제품 사진을 반환합니다. 이 스크립트는 adventureworks_demo.php 스크립트에서 호출됩니다.
photo.php 파일에 다음 코드를 넣습니다.
<?php
/*=============
This file is part of a Microsoft SQL Server Shared Source Application.
Copyright (C) Microsoft Corporation. All rights reserved.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============= */
$serverName = "(local)\sqlexpress";
$connectionInfo = array( "Database"=>"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.</br>";
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.</br>";
die(print_r( sqlsrv_errors(), true));
}
/* Free the statement and connection resources. */
sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );
?>