Complete guide to connecting Microsoft MSSQL (sql server) with php

When I was studying ezSQL, I saw mssql_connect() and other functions provided by PHP to connect to MSSQL. I thought that PHP, an open source and popular programming language around the world, should be easy to connect to Microsoft's data, but in reality When implementing it, I found many difficulties.

The PHP version I downloaded at the beginning was 5.93. After downloading and adding environment variables for a long time, the phpinfo() function finally successfully ran in the browser. Then when I searched all over the world for php_mssql.dll, I discovered that mssql is no longer natively supported in PHP versions 5.3 and above.

Finally found Microsoft Drivers 3.0 for PHP for SQL Server. I thought that Microsoft’s stuff should be able to do it, but I was helpless to find that SQLSRV30.EXE could not run: "SQLSRV30.EXE is not a valid win32 program. ".

After searching on the Internet for a long time, I summarized the following feasible methods, but before that you need to:

Configure MICROSOFT SQL SERVER

1 , download and install sql server. There are many versions now, ranging from 2000 to 2008. Find one and download it yourself.

2. Open the tcp/ip connection method so that the database can be accessed remotely. SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled

3. Open the data management interface and add users and databases.

4. Install php and configure IIS service.

5. Open the php.ini file in the folder where php is located, and add:

mssql.textlimit = 20971520
mssql.textsize = 20971520

After doing this, you can follow the following three methods to connect to the database:

Use the method that comes with php to connect to MSSQL (not applicable to 5.3 and later versions)

Make sure there is php_mssql.dll in the php ext extension library folder, and then in the configuration in PHP.ini ,Remove the ";" in front of

;extension=php_mssql.dll

.
Then you can test the connection:

//连接MSSQL
$conn=mssql_connect("实例名或者服务器IP","用户名","密码");

//测试连接
if($conn)
{
 echo "连接成功";
}

Microsoft Drivers for SQL Server for PHP
In July 2008, Microsoft released a new SQL Server for PHP connection Server driver, which improves some of the shortcomings of the MSSQL connection function that comes with PHP, and is developed in the form of a PHP extension plug-in. Through it, you can easily read and write Microsoft databases with PHP.

If your server is using IIS, then you must download it from here:

http://php.iis.net/

Because the above link is actually a network development platform integrated by Microsoft. It only provides online installation, but it easily integrates PDO plug-ins and php. Of course, there are also some other development functions of Microsoft, but if you don’t need it, you can No need to install it anymore, those are in visual studio.

But if you are using Apache, you can go here to download this plug-in directly. It is actually an unzipped file, and several DLL files are unzipped. The specific operations are as follows:

1 ) Download the driver package: http://www.microsoft.com/en-us/download/details.aspx?id=20098.

2) Extract the DLL file to PHP extension_dir directory, if it says that SQLSRV30.EXE is not a valid win32 program, it may be that some libraries are missing, it may be vc10, or it may not be run with administrator permissions.

extension_dir = “C:\PHP\ext”

3) Reference the corresponding dynamic link library file in the php.ini configuration file

extension=php_sqlsrv_52_ts_vc6.dll
extension=php_pdo_sqlsrv_52_ts_vc6.dll
extension=php_pdo.dll

The 52 and 53 represent the 5.2.x and 5.3.x versions of php. Choose the one that matches your php version;

The choice of vc6 or vc9 mainly depends on what web server software you are using. If you are using IIS, choose vc9, if you are using Apache, choose vc6.

As for ts and nts, it depends on whether the PHP version you installed is thread-safe or non-thread-safe. ts is thread-safe and nts is non-thread-safe.
4) Restart Apache
5) Connect to the database
Test connection code:

<?php
//本地测试的服务名
"(local)";
//使用sql server身份验证，参数使用数组的形式，一次是用户名，密码，数据库名
//如果你使用的是windows身份验证，那么可以去掉用户名和密码
$connectionInfo = array( "UID"=>"root",
    "PWD"=>"root2010",
    "Database"=>"master");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

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

Using FreeTDS under windows

What is FreeTDS? FreeTDS is actually an open source (or free) C program library, which can access and operate Microsoft's SQL database under a Linux system. It can be used in Sybase's db-lib or ct-lib library, which also contains an ODBC library. Allows many applications to connect to Sybase or Microsoft SQL servers. FreeTDS is released as source code, and because of this, it can be compiled and installed on almost any system.

If your server is a Windows system, then you should use php_dblib.dll. (more information on Using FreeTDS for Unix.)

Usually we can find these DLL files on this website - Frank Kromann's site, but it is basically out of date and will cause a lot of problems, so We recommend using PHP 5.2.x version under Windows, and take a look at the following suggestions:

1. Follow the table below to download php_dblib.dll and save it to the /PHP/ext folder.

#

PHP versionThread SafeFreeTDS versionDownload URLPHP 5.2.x (vc6)Yes0.82 + 20090302 patchesDownload!No0.82 + 20090302 patchesDownload!PHP 5.3.x (vc9)Yes0.82 + 20090904 patchesDownload!No0.82 + 20090904 patchesDownload!PHP 5.4.x (vc9)Yes0.82 + 20110906 patchesDownload! FTP Download!No0.82 + 20110906 patchesDownload! FTP Download!2、 FreeTDS 需要安装 .NET Framework v1.1 ，你可以到微软的网站去下载。或者你去Frank's site 下载需要的DLL文件，并保存到你的/PHP根目录下面。

3、在php配置文件 /PHP/php.ini 中添加：

extension=php_dblib.dll

4、当php引擎启动FreeTDS模块的时候需要传递一些信息，使得FreeTDS能够连接到它的默认的数据库。因此它的需要在freetds.conf中定义数据库连接的基本信息，该文件在其根目录下，可以按照你的情况来进行修改：

[global]
host = xxx.xxx.xxx.xxx (host name or ip of the MSSQL server)
port = 1433
client charset = UTF-8
tds version = 8.0
text size = 20971520

5、创建config.php文档来定义数据库连接参数：

$CFG->dbtype = 'mssql'; // Required
$CFG->dbhost = 'localhost'; // assuming MS SQL is on the same server, otherwise use an IP
$CFG->dbname = 'moodle';  // or whatever you called the database you created
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_';  //Prefix, you can change it, but NEVER leave it blank.

6、 重启你的网站，如果还是没有连接到你的数据库的话,在 /PHP/php.ini文件中将display_startup_errors改为"On"，当你解决了这些问题之后再将错误报告改为“Off”；

7、测试你的网站，建立test.php文件，代码如下，访问http://www.php.cn/进行测试

<?php
	$link = mssql_connect(&#39;localhost&#39;, &#39;db_user&#39;, &#39;db_password&#39;);
	if(!$link) {
		echo&#39;Could not connect&#39;;
		die(&#39;Could not connect: &#39; . mssql_error());
	}
	echo&#39;Successful connection&#39;;
	mssql_close($link);
?>

 以上就是php连接微软MSSQL(sql server)完全攻略 的内容，更多相关内容请关注PHP中文网（www.php.cn）！