PHP - Manual: Mssql

2024-12-23

简介 »

« msql

Microsoft SQL Server

简介
安装／配置
预定义常量
Mssql 函数
- mssql_bind — Adds a parameter to a stored procedure or a remote stored procedure
- mssql_close — 关闭MS SQL Server链接
- mssql_connect — 打开MS SQL server链接
- mssql_data_seek — Moves internal row pointer
- mssql_execute — Executes a stored procedure on a MS SQL server database
- mssql_fetch_array — Fetch a result row as an associative array, a numeric array, or both
- mssql_fetch_assoc — Returns an associative array of the current row in the result
- mssql_fetch_batch — Returns the next batch of records
- mssql_fetch_field — Get field information
- mssql_fetch_object — Fetch row as object
- mssql_fetch_row — Get row as enumerated array
- mssql_field_length — Get the length of a field
- mssql_field_name — Get the name of a field
- mssql_field_seek — Seeks to the specified field offset
- mssql_field_type — Gets the type of a field
- mssql_free_result — Free result memory
- mssql_free_statement — Free statement memory
- mssql_get_last_message — Returns the last message from the server
- mssql_guid_string — Converts a 16 byte binary GUID to a string
- mssql_init — Initializes a stored procedure or a remote stored procedure
- mssql_min_error_severity — Sets the minimum error severity
- mssql_min_message_severity — Sets the minimum message severity
- mssql_next_result — Move the internal result pointer to the next result
- mssql_num_fields — Gets the number of fields in result
- mssql_num_rows — Gets the number of rows in result
- mssql_pconnect — Open persistent MS SQL connection
- mssql_query — Send MS SQL query
- mssql_result — Get result data
- mssql_rows_affected — Returns the number of records affected by the query
- mssql_select_db — Select MS SQL database

add a note

User Contributed Notes 16 notes

down

exidas at madnes dot eu ¶

9 years ago


Hi, i was need some short and simple script to list all tables and columns of MSSQL database. There was nothing easy to explain on the net, so i've decided to share my short script, i hope it will help.



<?php

$all = MSSQL_Query("select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME, ORDINAL_POSITION");



$tables = array();

$columns = array();



while($fet_tbl = MSSQL_Fetch_Assoc($all)) { // PUSH ALL TABLES AND COLUMNS INTO THE ARRAY



  $tables[] = $fet_tbl[TABLE_NAME];

  $columns[] = $fet_tbl[COLUMN_NAME];



}



$sltml = array_count_values($tables); // HOW MANY COLUMNS ARE IN THE TABLE



foreach($sltml as $table_name => $id) {

 

 echo "<h2>". $table_name ." (". $id .")</h2><ol>";

 

    for($i = 0; $i <= $id-1; $i++) {

    

    echo "<li>". $columns[$i] ."</li>";

    

    }

    

  echo"</ol>";

 

}

?>

down

marques at displague dot com ¶

4 years ago


I made this wrapper a few years ago to handle mssql -> sqlsrv_pdo migrations.   It's a drop-in include that provides the mssql_ functions using a sqlsrv_pdo implementation.

https://github.com/displague/mssql_helper/blob/master/mssql_helper.php

down

Fabian G ¶

9 months ago


Instead of using Mssql or DBLib extension you should use the official extensions from Microsoft from here: https://github.com/Microsoft/msphpsql

down

bryanpiercecap at gmail dot com ¶

7 years ago


Here is version 3.0 of the MSSQL PHP driver.
1.1 is severely deprecated. 

http://www.microsoft.com/download/en/details.aspx?id=17308

down

Anonymous ¶

3 years ago


I really don't know why these functions are not deprecated yet. There isn't even a built-in way to escape your values, let alone use parameterized queries.

On Windows servers, you should use SqlSrv (http://php.net/sqlsrv), an alternative driver for MS SQL is available from Microsoft: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx.

On Linux servers, you should use PDO_DBLIB (http://php.net/pdo-dblib).

down

michal dot kocarek at brainbox dot cz ¶

9 years ago


Microsoft has issued in nearly past Native SQL driver for PHP.
This driver works with MSSQL 2000, 2005 and 2008 servers. Driver is issued as PHP extension. Source codes also available.

The driver supports native conversion to UTF-8, scrollable cursors and other features which this (old) library does not.

For more information and extension download please see
http://www.codeplex.com/SQLSRVPHP
They have also blog on http://blogs.msdn.com/sqlphp/

down

thanhha dot phan at yahoo dot com ¶

10 years ago


I got a serious problem with Unicode data when working with PHP and MSSQL. I have to say the PHP and MSSQL doesn't seem to be a good combination.

MSSQL is really complicated to work with since the whole server uses a single character encoding[ucs-2]. In order to store unicode information, the column data types must be specified as one of the national character types, NVARCHAR, NCHAR or NTEXT. 

In order to retrieve data saved in unicode format, M$ suggest users to cast columns to binary data.Unfortunately php_mssql extension doesn't support  binary data to be returned in string columns.

My advice is to use FreeTDS driver [it's just an php extension]. This driver seems to handle unicode data better. I can put and receive unicode with casting or change encoding.

A good tutorial "Using freeTDS" can be found here

http://docs.moodle.org/en/Installing_MSSQL_for_PHP

However, freeTDS is not a perfect solution for unicode data. I'm not able to execute stored procedures with mssql_bind, mssql_bind without having my text changed.

down

-1

beartenor1 ¶

9 years ago


As of October 2009, the new name is "SQL Server Driver for PHP 1.1"

Go to Microsoft Download Center http://www.microsoft.com/downloads and search 
for "SQL Server Driver for PHP" which should take you to the following link:

http://www.microsoft.com/downloads/details.aspx?displaylang=en
&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9

But I wouldn't trust it to stay there. Microsoft has been moving a lot of download links lately.

down

-2

opc dot three at gmail dot com ¶

10 years ago


After extensive research trying to get PHP on Linux communicating with SQL Server 2005 and 2008 including support for all Unicode, MAX and XML data types I could not find any open source solutions...yes, I spent a lot of time trying to get FreeTDS to work to no avail.

I found one free solution that runs on Windows which is to use the "SQL Server Driver for PHP" provided by Microsoft (http://sql2k5php.codeplex.com). The driver relies on the Microsoft Native Client ODBC drivers for SQL Server 2008 (part of the "Microsoft SQL Server 2008 Native Client" which is downloadable from Microsoft) which is why this solution will not work on anything except Windows.

I did find a solution that works for PHP on Linux but it's not free...use the standard PHP::ODBC lib (free) and the Easysoft ODBC driver for SQL Server (not free, but reasonable by Enterprise standards). You can check out the ODBC driver by going here http://www.easysoft.com/products/data_access and looking for "Easysoft ODBC-SQL Server Driver"

down

-3

vexx at pisem dot net ¶

8 years ago


this function get inserted ident like function mysql_insert_id()



<?php

function mssql_insert_id() {

    $id = 0;

    $res = mssql_query("SELECT @@identity AS id");

    if ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

        $id = $row["id"];

    }

    return $id;

}

?>

down

-2

dimirji1995 at gmail dot com ¶

2 years ago


I spent many days trying to bind PHP with Linux and SQL Server but failed. I saw that the last update about that issue was 7 years ago.

I want to update that theme for everyone who is in searches for the reliable solution.  I didn't find a free and simple way to do that. 

I tried a trial version of SQL Server ODBC driver from Devart company and it turned out to be compatible with PHP and has a version for Linux. The driver has a simple configuration and works with PHP on Linux. But it's big minus that it is not free.

down

-4

AA ¶

10 years ago


On windows, do not use this for mssql 2005 of later.  The methods of access are deprecated.

Use the Microsoft SQL Server 2005 Driver for PHP instead.
Current link is.
http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx

down

-8

ccsalway at yahoo dot co dot uk ¶

9 years ago


Im running PHP 5.3 and used the php development ini and when I use the Microsoft SQL for PHP driver, I get the following error:

[15-Aug-2009 07:48:13] PHP Warning:  PHP Startup: sqlsrv: Unable to initialize module
Module compiled with module API=20060613
PHP    compiled with module API=20090626
These options need to match
 in Unknown on line 0

down

-5

sakthivelmca74 at gmail dot com ¶

1 year ago


<?php

$server = "xxxxx";  //server name   ="welcome"
$user = "xxxxx";     //user name     ="std"
$pass = "xxxxxx";   // password      ="3434&^%"
$mydb = "xxxxxxxxx"; //database name  ="std"

 

$con = mssql_connect($server, $user, $pass) or die("Couldn't connect to MSSQL Server on $server");
$db =  mssql_select_db($mydb, $con) or die("Couldn't open database $mydb");
?>

down

-7

tummen at jgd dot se ¶

9 years ago


i struggled with sqlsrv_connect for some time, first i had to get native client 2008 to get it to work, 2005 was not enough.

then i got som note that dll was compiled with wrong version, so i had to use test version 1.1 istead of 1.0 of the client and pick the version called php_sqlsrv_53_ts_vc6.dll

but still no success with connect.

finally i spent 7 hours testing things before i found out:

$serverName = "(local)"; ERROR for me

$serverName = "localhost\myinstansofdatabase"; //WOHOOOO

down

-12

jezza at 9000internets dot com ¶

8 years ago


Using the new MS driver I have come across a difference between the array that I get back compared to mssql.



Example is here (both return types are associative arrays:



sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC)

"same as" sqlsrv_fetch($resource), as this returns by default an associative array, by default sqlsrv_fetch_array returns both an associative and a numeric array.



returns...



array(6) { ["yaxis"]=> object(DateTime)#58 (3) { ["date"]=> string(19) "2010-09-29 00:00:00" ["timezone_type"]=> int(3) ["timezone"]=> string(19) "Australia/Melbourne" } ["xaxis"]=> string(7) "Inbound" ["tt"]=> int(280345) ["ht"]=> int(297219) ["wt"]=> int(16874) ["calls"]=> int(1539) } string(8) ""



mssql_fetch_assoc($resource)



returns



array(6) { ["yaxis"]=> string(19) "2010-09-29 00:00:00" ["xaxis"]=> string(7) "Inbound" ["tt"]=> int(280345) ["ht"]=> int(297219) ["wt"]=> int(16874) ["calls"]=> int(1539) } string(8) ""



it would be much too difficult to change a large number of applications to compensate for this.



Found my answer...

add this to your connection objects parameters on instantiation



"ReturnDatesAsStrings"=>true



Found it buried in this article: http://msdn.microsoft.com/en-us/library/ee376928(SQL.90).aspx

add a note

官方地址：https://www.php.net/manual/en/book.mssql.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络数据库 JAVA 面试 NoSQL C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 PHP 8.1 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 ADORecordSet对象 adodb手册 opcache预加载 Composer的Packagist资源 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet mysql面试题 PHP json解析（json_decode）页面工具 php中几种注释规范纯php实现DES以及TripleDES加密算法 PHP5 扩展SOAP 调用 webservice [鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式 [原创]诡异的php-fpm超时问题两千行PHP学习笔记

略微加速

PHP官方手册 - 互联网笔记

Microsoft SQL Server

User Contributed Notes 16 notes