예제 응용 프로그램
AdventureWorks Product Reviews 예제 응용 프로그램은 SQL Server Driver for PHP의 기능을 보여 주는 웹 응용 프로그램입니다. 사용자는 응용 프로그램에서 키워드를 입력하여 제품을 검색하고 선택한 제품에 대한 검토를 확인 및 작성할 수 있으며 선택한 제품의 이미지를 업로드할 수 있습니다.
예제 응용 프로그램 실행
SQL Server Driver for PHP를 설치합니다. 자세한 내용은 시작을 참조하십시오.
이 문서 뒷부분에 나와 있는 코드를 adventureworks_demo.php 및 photo.php 두 파일에 복사합니다.
adventureworks_demo.php 및 photo.php 파일을 사용자 웹 서버의 루트 디렉터리에 넣습니다.
브라우저에서 https://localhost/adventureworks_demo.php를 시작하여 응용 프로그램을 실행합니다.
요구 사항
AdventureWorks Product Reviews 예제 응용 프로그램을 실행하려면 컴퓨터에서 다음과 같은 조건이 충족되어야 합니다.
- 시스템이 SQL Server Driver for PHP 요구 사항을 충족합니다. 자세한 내용은 시스템 요구 사항(SQL Server Driver for PHP)을 참조하십시오.
- adventureworks_demo.php 및 photo.php 파일이 사용자 웹 서버의 루트 디렉터리에 있습니다. 두 파일은 이 문서의 뒷부분에 나와 있는 코드를 포함해야 합니다.
- SQL Server 2005 또는 SQL Server 2008이 AdventureWorks 데이터베이스에 연결된 상태로 로컬 컴퓨터에 설치되어 있습니다.
- 웹 브라우저가 설치되어 있습니다.
데모
AdventureWorks Product Reviews 예제 응용 프로그램에서는 다음과 같은 방법을 보여 줍니다.
- Windows 인증을 사용하여 SQL Server에 대한 연결을 여는 방법
- sqlsrv_query를 사용하여 매개 변수가 있는 쿼리를 실행하는 방법
- sqlsrv_prepare 및 sqlsrv_execute를 조합하여 매개 변수가 있는 쿼리를 준비하고 실행하는 방법
- sqlsrv_fetch_array를 사용하여 데이터를 검색하는 방법
- sqlsrv_fetch 및 sqlsrv_get_field를 조합하여 데이터를 검색하는 방법
- 데이터를 스트림으로 검색하는 방법
- 데이터를 스트림으로 보내는 방법
- 오류를 확인하는 방법
예
AdventureWorks Product Reviews 예제 응용 프로그램에서는 이름에 사용자가 입력한 문자열이 들어 있는 제품에 대한 정보를 데이터베이스에서 반환합니다. 사용자는 반환된 제품 목록에서 선택한 제품에 대한 검토 확인, 이미지 확인, 이미지 업로드 및 검토 작성을 수행할 수 있습니다.
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
Microsoft SQL Server 2005 Driver for PHP.</h5><br/>
<?php
$serverName = "(local)";
$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':
$query = $_REQUEST['query'];
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice
FROM Production.Product
WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0";
$getProducts = sqlsrv_query( $conn,
$tsql,
array( $query ));
if ( $getProducts === false)
{ die( FormatErrors( sqlsrv_errors() ) ); }
$headings = array("Product ID",
"Product Name",
"Color",
"Size",
"Price");
BeginProductsTable( "Query Results", $headings );
$productCount = 0;
while( $row = sqlsrv_fetch_array( $getProducts,
SQLSRV_FETCH_ASSOC))
{
PopulateProductsTable( $row );
$productCount++;
}
EndProductsTable();
if ( $productCount == 0 )
{
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( $_REQUEST['productid'] );
GetReviews( $conn, $_REQUEST['productid'] );
sqlsrv_close( $conn );
break;
/* Write a review for a specified productID. */
case 'writereview':
DisplayWriteReviewForm( $_REQUEST['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,".$_REQUEST['comments'];
$stream = fopen( $comments, "r" );
$tsql = "INSERT INTO Production.ProductReview (ProductID,
ReviewerName,
ReviewDate,
EmailAddress,
Rating,
Comments)
VALUES (?,?,?,?,?,?)";
$params = array(&$_REQUEST['productid'],
&$_REQUEST['name'],
date("Y-m-d"),
&$_REQUEST['email'],
&$_REQUEST['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, $_REQUEST['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($_REQUEST['productid']));
if( $getName === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
if ( sqlsrv_fetch( $getName ) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
$name = sqlsrv_get_field( $getName, 0);
DisplayUploadPictureForm( $_REQUEST['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");
/* Turn off the default behavior of sending all stream data
to the server at the time of query execution. */
$options = array("SendStreamParamsAtExec"=>0);
$uploadPic = sqlsrv_prepare($conn, $tsql, array(
array(&$fileStream,
SQLSRV_PARAM_IN,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
SQLSRV_SQLTYPE_VARBINARY('max'))),
$options);
if( $uploadPic === false )
{ die( FormatErrors( sqlsrv_errors() ) );}
if( sqlsrv_execute($uploadPic) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
/* Stream data to the database in chunks. */
while( $success = sqlsrv_send_stream_data( $uploadPic))
{
}
/*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 = ?";
if( sqlsrv_query($conn, $tsql, array($photoID,
$_REQUEST['productid'])) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
GetPicture( $_REQUEST['productid']);
DisplayWriteReviewButton( $_REQUEST['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."'/></td></tr>";
echo "<tr align='center'><td><a href='?action=displaypicture&
productid=".$productID."'>Upload new picture.</a></td></tr>";
echo "</td></tr></table>
";
}
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";
$getReview = sqlsrv_query( $conn, $tsql, array($productID));
if ( $getReview === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
$reviewCount = 0;
while ( sqlsrv_fetch( $getReview ) )
{
$name = sqlsrv_get_field( $getReview, 0 );
$date = sqlsrv_get_field( $getReview, 1 );
$rating = sqlsrv_get_field( $getReview, 2 );
/* Open comments as a stream. */
$comments = sqlsrv_get_field( $getReview, 3,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR));
DisplayReview( $productID,
$name,
$date,
$rating,
$comments );
$reviewCount++;
}
if( $reviewCount == 0 )
{ DisplayNoReviewsMsg(); }
DisplayWriteReviewButton( $productID );
sqlsrv_free_stmt( $getReview );
}
/*** Presentation and Utility Functions ***/
function BeginProductsTable( $tableName, $headings )
{
/* Display the beginning of the search results table. */
echo "<table align='center' cellpadding='5'>";
echo "<tr bgcolor='silver'>$tableName</tr><tr>";
foreach ( $headings as $heading )
{
echo "<td>$heading</td>";
}
echo "</tr>";
}
function DisplayNoProdutsMsg()
{
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>
";
}
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'/>
</p>
</td>
</tr>
</form>
</table>";
}
function EndProductsTable()
{
echo "</table>
";
}
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'/></p>
</td></tr>
</form></td></tr>";
}
function RoundPrice( $value )
{
/* Remove precision (last two zeros) from list price. */
return substr( $value, 0, -2 );
}
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>
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)";
$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. */
if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field( $stmt, 0,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
fpassthru($image);
}
else
{
echo "Error in retrieving data.</br>";
die(print_r( sqlsrv_errors(), true));
}
/* Free the statement and connectin resources. */
sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );
?>
참고 항목
개념
관련 자료
서버에 연결
실행 함수 비교
데이터 검색
데이터 업데이트(SQL Server Driver for PHP)
API 참조(SQL Server Driver for PHP)