为了能让PHP连接MSSQL,系统需要安装MSSQL,PHP,且在PHP.ini中的配置中,将
;extension=php_mssql.dll前面的;去掉
1.连接MSSQL
$conn=mssql_connect("实例名或者服务器IP","用户名","密码");
//测试连接
if($conn)
{
echo "连接成功";
}
2.选择要连接的数据库
mssql_select_db("dbname");
3.执行查询
$rs = mssql_query("select top 1 id,username from tbname",$conn);
或者直接执行update,insert等语句,可以不用为返回结果赋值
mssql_query("update tbname set username='niunv' where id=1");
4.获取记录集行数
echo mssql_num_rows($rs);
5.获取记录集
if($row=mssql_fetch_array($rs))
{
$id = $row[0];//获取ID字段值
$username = $row[1];//获取username字段值
}
6.获取新增记录的ID
将id字段设置为IDENTITY字段,执行insert语句以后,就会产生一个 @@IDENTITY 全局变量值,查询出来就是最后一条新增记录的ID了.
mssql_query("insert into tbname(username) values ('nv')",$conn);
$rs = mssql_query("select @@IDENTITY as id",$conn);
if($row=mssql_fetch_array($rs))
{
echo $row[0];
}
7.释放记录集
mssql_free_result($rs);
8.关闭连接
mssql_close($conn);
注:用PHP操作MSSQL比在ASP连接MYSQL要简单,所以,当需要MSSQL与MYSQL并存时,用PHP连接MSSQL来操作MYSQL与 MSSQL并存比较简单好用.如果是ASP连接MYSQL,需要安装一个MYSQL驱动,默认windows的ODBC没有安装,很遗憾...
1.在web服务器上至少安装了mssql的客户端
2.打开php.ini把;extension=php_mssql.dll 前面的分号去掉
有必要话:需要制定extension_dir
3.推荐使用 php<=4.0.9 <=5.0.3目前 我还没有连接成功过4.010和 5.0.3
4.数据库的 连接分页可以 到 phpe.net上获取到相应的class
下面是我根据那里 修改的 一个class
- <?php
- class SQL{
- var $server ;
- var $userName ;
- var $passWord ;
- var $dataBase ;
- var $linkID = 0;
- var $queryResult ;
- var $lastInsertID ;
- var $pageNum = 0; //分页用---共有几条数据
- var $ER ;
- function SQL( $Server = '' , $UserName = '' , $PassWord = '' , $DataBase = '' ){
- $this ->server = $Server ;
- $this ->userName = $UserName ;
- $this ->passWord = $PassWord ;
- $this ->dataBase = $DataBase ;
- }
- function db_connect(){
- $this ->linkID = mssql_pconnect( $this ->server, $this ->userName, $this ->passWord);
- if (! $this ->linkID){
- $this ->ER = "db_connect($this->server,$this->userName,$this->passWord) error" ;
- return 0;
- }
- if (!mssql_select_db( $this ->dataBase, $this ->linkID)) {
- $this ->ER = "mssql_select_db($this->dataBase,$this->lastInsertID) error" ;
- return 0;
- }
- return $this ->linkID;
- }
- function selectDatabase(){
- if (mssql_select_db( $this ->dataBase))
- return 1;
- else
- return 0;
- }
- function query( $Str ){
- if ( $this ->linkID == 0) {
- $this ->ER = "数据库还没有连接!!" ;
- }
- $this ->queryResult = mssql_query( $Str );
- //$this->queryResult = mssql_query($Str,$this->linkID);
- if (! $this ->queryResult) {
- $this ->ER = "$Str.没有操作成功,query error!!" ;
- return 0;/
- function fetch_array( $result ){
- if ( $result != "" ) $this ->queryResult = $result ;
- $rec =mssql_fetch_array( $this ->queryResult);
- if ( is_array ( $rec )){
- return $rec ;
- }
- //$this->ER = "没有获取数据!";
- return 0;
- }
- function freeResult( $result = "" ){
- if ( $result != "" ) $this ->queryResult = $result ;
- return mssql_free_result( $this ->queryResult);
- }
- function num_rows( $result = "" ){
- if ( $result != "" ) {
- $this ->queryResult = $result ;
- $row = mssql_num_rows( $this ->queryResult);
- return $row ;
- }
- }
- function result_ar( $str = '' ){
- if ( empty empty ( $str )) {
- return 0;
- }
- $back = array ();
- $this ->queryResult = $this ->query( $str );
- while ( $row = $this ->fetch_array( $this ->queryResult)) {
- $back [] = $row ;
- }
- return $back ;
- }
- function page( $Str , $Page =0, $ShowNum =5){
- $back = array (); //返回数据
- $maxNum = 0;
- if ( $Str == "" ) {
- $this ->ER = "没有数据" ;
- return 0;
- }
- $this ->queryResult = $this ->query( $Str );
- if ( $this ->queryResult){
- if ( $Page == "" ){
- $nopa =0;
- } else {
- $nopa = ( $Page -1)* $ShowNum ;
- if ( $nopa <0) {
- $nopa = 0;
- }
- }
- $maxNum = $this ->num_rows( $this ->queryResult);
- $k =0;
- $i =0;
- $dd = $this ->fetch_array( $this ->queryResult);
- while ( $dd && $nopa <= $maxNum && $i < $ShowNum ){
- if ( $nopa >= $maxNum ) $nopa = $maxNum ;
- mssql_data_seek( $this ->queryResult, $nopa );
- $row = $this ->fetch_array( $this ->queryResult);
- $nopa ++;
- $i ++;
- $back [] = $row ;
- if ( $nopa >= $maxNum ) {
- break ;
- }
- }
- }
- $this ->pageNum = $maxNum ;
- return $back ;
- }
- function page_html( $DataNum =0, $Page =1, $ShowNum =3, $web , $Post = '' ){
- if ( $DataNum == 0) {
- $back = "没有要查询的数据" ;
- } else {
- if ( $ShowNum <=0) {
- $ShowNum = 3;
- }
- if ( $Page <=0) {
- $Page = 1;
- }
- if ( empty empty ( $web )) {
- $web = "#" ;
- }
- $pageNum = ceil ( $DataNum / $ShowNum );
- if ( $Page <= 1) {
- $top = "首页<<" ;
- } else {
- $top = "<a href='" . $web . "?page=0&" . $Post . "' target='_self'>首页<< </a>" ;
- }
- if ( $Page !==1) {
- $upPage = "<a href='" . $web . "?page=" .( $Page -1). "&" . $Post . "' target='_self'>上一页</a>" ;
- } else {
- $upPage = "上一页" ;
- }
- if ( $Page < $pageNum ) {
- $downPage = "<a href='" . $web . "?page=" .( $Page +1). "&" . $Post . "' target='_self'>下一页</a>" ;
- } else {
- $downPage = "下一页" ;
- }
- if ( $Page == $pageNum ) {
- $foot = ">>尾页" ;
- } else {
- $foot = "<a href='" . $web . "?page=" . $pageNum . "&" . $Post . "' target='_self'> >>尾页</a>" ;
- }
- $back = <<<EOT
- 共 $pageNum 页
- 第 $Page / $pageNum 页 $top $upPage $downPage $foot
- EOT;
- }
- return $back ;
- }
- } //end class
- ?>