当前位置：首页 > article >正文

php7.4.3连接MSsql server方法

article 2026/1/31 16:19:19

需要下载安装Microsoft Drivers for PHP for SQL Server驱动，

https://download.csdn.net/download/tjsoft/90568178

实操Win2008IISphp7.4.3连接SqlServer2008数据库所有安装包资源-CSDN文库

适用于 SQL Server 的 PHP 的 Microsoft 驱动程序支持与 SQL Server for PHP 应用程序的集成。驱动程序是 PHP 扩展，允许从 PHP 脚本中读取和写入 SQL Server 数据。这些驱动程序提供了用于访问 Azure SQL 数据库以及 SQL Server 2005 及更高版本的所有版本（包括 Express Edition）中的数据的接口。这些驱动程序利用 PHP 功能（包括 PHP 流）来读取和写入大型对象。

地址：https://msdn.microsoft.com/library/dn865013.aspx。下载后解压放到php对应的ext目录下。然后打开php.ini文件，在extension 后面添加一下配置

extension=php_pdo_sqlsrv_7_ts.dll

extension=php_sqlsrv_7_ts.dll

重启IIS,查看phpinfo()，确保apache已经支持sqlsrv。如下图所示：

<?php

echo phpinfo();

?>

php连接MSsql server方法

并且安装sqlncli.msi，这个文件是协助windows环境访问sql server所在的数据库服务器的

php连接MSsql server方法

三、通过odbc方式连接sqlserver系列。

需要在php.ini中开启php_pdo_odbc.dll扩展。

php连接MSsql server方法

四、通过PDO方式连接sqlserver。

在php.ini中开启php_pdo_mssql.dll扩展。在phpinfo中可查看

php连接MSsql server方法

五、通过COM方式连接。

下面是实现代码：

php连接MSsql server方法

此示例从数据库中返回用户输入的字符串包含在名称中的产品的信息。从返回的产品列表中，用户可以看到评论，查看图片，上传图片，并对选定的产品撰写评论。

将以下代码放入名为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 );  
?>

另请参阅

Microsoft Drivers for PHP for SQL Server - PHP drivers for SQL Server | Microsoft Learn

php7.4.3连接MSsql server方法

LOB 示例

另请参阅

相关文章：

php7.4.3连接MSsql server方法

Flask返回文件方法详解

JS中的Promise对象

macOS设置定时播放眼保健操

Python 小练习系列 | Vol.14：掌握偏函数 partial，用函数更丝滑！

记录学习的第二十三天

Web品质 - 重要的HTML元素

SpringBoot整合sa-token，Redis：解决重启项目丢失登录态问题

Python 字典和集合（子类化UserDict）

npm fund 命令的作用

ES：账号、索引、ILM

哈希表(开散列)的实现

#在docker中启动mysql之类的容器时,没有挂载的数据...在后期怎么把数据导出外部

[蓝桥杯] 挖矿（CC++双语版）

Johnson算法流水线问题 java实现

远程监控系统项目里练习

安装并配置Maven

PlatformIO 自定义脚本选择编译库源文件 - 设置只用于C++ 的编译选项

dolphinscheduler单机部署链接oracle

MongoDB常见面试题总结（上）

java基础迭代Iterable接口以及迭代器Iterator

CentOS禁用nouveau驱动

Linux 时间同步工具 Chrony 简介与使用

C语言：字符串处理函数strstr分析

28--当路由器开始“宫斗“：设备控制面安全配置全解

Vue知识点（5）-- 动画

MATLAB2024a超详细图文安装教程（2025最新版保姆级教程）附安装钥

基于 Spring Boot 瑞吉外卖系统开发（二）

软考系统架构设计师之大数据与人工智能笔记

146. LRU 缓存带TTL的LRU缓存实现(拓展)