- $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
- /**
- *mssql 数据库连接类
- **/
- 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;
- }
-
- /**public
- * function: Check the database, if exist then select
- * exist: return 1
- * not exist: return 0
- */
- 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;//****************对于php 4.3.9以上版本的错误用1
- }
- return $this->queryResult;
- }
-
- /**
- *数据获取
- **/
- 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;
- }
-
- /**public
- * function: Free the Query Result
- * success return 1
- * failed: 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($str)) {
- return 0;
- }
- $back = array();
- $this->queryResult = $this->query($str);
-
- while ($row = $this->fetch_array($this->queryResult)) {
- $back[] = $row;
- }
- return $back;
- }
-
- /**
- *数据库信息分页
- *$Result 数据库操作
- *str ==sql语句
- *page ==第几页
- *showNum ==显示几页
- */
- 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;
- }
-
- /**
- *分页的html页码
- */
- 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($web)) {
- $web = "#";
- }
- $pageNum = ceil($DataNum/$ShowNum);
- if ($Page <= 1) {
- $top = "首页<<";
- }else {
- $top = "首页<< ";
- }
- if ($Page !==1) {
- $upPage = "上一页";
- }else {
- $upPage = "上一页";
- }
- if ($Page < $pageNum) {
- $downPage = "下一页";
- }else {
- $downPage = "下一页";
- }
- if ($Page == $pageNum) {
- $foot = ">>尾页";
- }else {
- $foot = " >>尾页";
- }
-
- $back = <<
- 共 $pageNum 页
- 第 $Page/$pageNum 页 $top $upPage $downPage $foot
- EOT;
- }
- return $back;
- }
- }//end class
- ?>
|