最新消息:觉得本站不错的话 记得收藏哦 博客内某些功能仅供测试 讨论群:135931704 快养不起小站了 各位有闲钱就打赏下把 My Email weicots#gmail.com Please replace # with @

使用 PHP 链接 SQL Server

MSSQL ajiang-tuzi 2437浏览

下载

PHP 官方
针对各数据库系统对应的扩展 SQLSRV 安装/配置
http://php.net/manual/en/pdo.drivers.php

Microsoft 官方扩展
Microsoft Drivers for PHP for SQL Server

配置方法

参考一

Quoting http://php.net/manual/en/intro.mssql.php:

The MSSQL extension is not available anymore on Windows with PHP 5.3 or later. SQLSRV, an alternative driver for MS SQL is available from Microsoft: ? http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx.

Once you downloaded that, follow the instructions at this page:

In a nutshell:

Put the driver file in your PHP extension directory.
Modify the php.ini file to include the driver. For example:

extension=php_sqlsrv_53_nts_vc9.dll  

Restart the Web server.

See Also (copied from that page)

The PHP Manual for the SQLSRV extension is located athttp://php.net/manual/en/sqlsrv.installation.php and offers the following for Installation:

The SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file. The SQLSRV download comes with several driver files. Which driver file you use will depend on 3 factors: the PHP version you are using, whether you are using thread-safe or non-thread-safe PHP, and whether your PHP installation was compiled with the VC6 or VC9 compiler. For example, if you are running PHP 5.3, you are using non-thread-safe PHP, and your PHP installation was compiled with the VC9 compiler, you should use the php_sqlsrv_53_nts_vc9.dll file. (You should use a non-thread-safe version compiled with the VC9 compiler if you are using IIS as your web server). If you are running PHP 5.2, you are using thread-safe PHP, and your PHP installation was compiled with the VC6 compiler, you should use the php_sqlsrv_52_ts_vc6.dll file.

The drivers can also be used with PDO.

参考2
Download Microsoft Drivers for PHP for SQL Server. Extract the files and use one of:

File                             Thread Safe         VC Bulid
php_sqlsrv_53_nts_vc6.dll           No                  VC6
php_sqlsrv_53_nts_vc9.dll           No                  VC9
php_sqlsrv_53_ts_vc6.dll            Yes                 VC6
php_sqlsrv_53_ts_vc9.dll            Yes                 VC9

You can see the Thread Safety status in phpinfo().
Add the correct file to your ext directory and the following line to your php.ini:

extension=php_sqlsrv_53_*_vc*.dll

Use the filename of the file you used.
As Gordon already posted this is the new Extension from Microsoft and uses the sqlsrv_* API instead of mssql_*
Update:
On Linux you do not have the requisite drivers and neither the SQLSERV Extension.
Look at Connect to MS SQL Server from PHP on Linux? for a discussion on this.
In short you need to install FreeTDS and YES you need to use mssql_* functions on linux. see update 2
To simplify things in the long run I would recommend creating a wrapper class with requisite functions which use the appropriate API (sqlsrv_* or mssql_*) based on which extension is loaded.
Update 2: You do not need to use mssql_* functions on linux. You can connect to an ms sql server using PDO + ODBC + FreeTDS. On windows, the best performing method to connect is via PDO + ODBC + SQL Native Client since the PDO + SQLSRV driver can be incredibly slow.

The SQLSRV Extension from microsoft is only available for windows. For using a MS SQL Server from a Linux Web Server you need to use a third party driver as Microsoft doesnt provide one. Look at this question stackoverflow.com/questions/507479/….

示范

Rename the files under all PHP versions to:
php_pdo_sqlsrv.dll
php_sqlsrv.dll

Edit file php.ini, and insert the loading of “php_sqlsrv.dll”…

[PHP_SQLSRV]
extension=php_sqlsrv.dll
extension=php_pdo_sqlsrv.dll
Save file. Restart Apache.

测试代码

我的测试代码
PHP 使用 SQL Server 查询 以及常见错误
Example #1 Connect using Windows Authentication.

<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"dbName");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Example #2 Connect by specifying a user name and password.

<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Example #3 Connect on a specifed port.

<?php
$serverName = "serverName\sqlexpress, 1542"; //serverName\instanceName, portNumber (default is 1433)
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

官方示例

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
                   procedural API (SQLSRV driver) of the Microsoft
                   Drivers for PHP for SQL Server.</h5>

<?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
{
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 "
<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 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'/>  

</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'>  

<input type='submit' name='submit' value='Submit Review'/>
</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'/>
            </td>
</tr>

            </form>
</td>
</tr>

";
}  

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information:
";  

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."
";
        echo "Code: ".$error['code']."
";
        echo "Message: ".$error['message']."
";
    }
}
?>
</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)\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 connectin resources. */
sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );
?>
<a href="https://msdn.microsoft.com/zh-cn/library/cc296196.aspx" target="_blank">MSDN 官方参考文档</a>

参考及引用
PHP 官方
针对各数据库系统对应的扩展 SQLSRV 安装/配置
http://php.net/manual/en/pdo.drivers.php

Microsoft 官方扩展
Microsoft Drivers for PHP for SQL Server

转载请注明:(●--●) Hello.My Weicot » 使用 PHP 链接 SQL Server