範例應用程式

發行項
11/05/2009

AdventureWorks 產品評論範例應用程式為 Web 應用程式，將示範 SQL Server Driver for PHP 的功能。此應用程式可讓使用者輸入關鍵字來搜尋產品、閱讀所選產品的評論、針對所選產品發表評論與上載所選產品的影像。

執行範例應用程式

安裝 SQL Server Driver for PHP。如需詳細資訊，請參閱＜使用者入門＞。
請複製本文件稍後列出的程式碼至兩個檔案中：adventureworks_demo.php 與 photo.php。
將 adventureworks_demo.php 與 photo.php 檔置於您 Web 伺服器的根目錄下。
從瀏覽器開啟 https://localhost/adventureworks_demo.php 以執行應用程式。

需求

若要執行 AdventureWorks 產品評論範例應用程式，您的電腦必須具備下列條件：

您的系統符合 SQL Server Driver for PHP 的需求。如需詳細資訊，請參閱＜系統需求 (SQL Server Driver for PHP)＞。
adventureworks_demo.php 與 photo.php 檔位於您 Web 伺服器的根目錄下。這些檔案必須包含本文件稍後列出的程式碼。
本機電腦上安裝了 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 產品評論範例應用程式將從資料庫傳回產品資訊 (產品名稱中需包含使用者輸入的字串)。從傳回的產品清單中，使用者可以針對所選產品閱讀評論、觀賞影像、上載影像並發表評論。

將下列程式碼貼在名為 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 指令碼呼叫。

將下列程式碼貼在名為 named 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)

Share via

範例應用程式

執行範例應用程式

需求

示範

範例

另請參閱

概念

其他資源

其他資源