Home > Article > Backend Development > Some related experiences and precautions in connecting php to mssql_PHP tutorial

Some related experiences and precautions in connecting php to mssql_PHP tutorial

WBOYOriginal: 2016-07-21 15:13:42890browse

In order to allow PHP to connect to MSSQL, the system needs to install MSSQL and PHP, and in the configuration in PHP.ini, remove the
in front of extension=php_mssql.dll 1. Connect to MSSQL

Copy code The code is as follows:

$conn=mssql_connect("Instance name or server IP", "Username", "Password"); 
//Test connection
if($conn) 
{ 
echo "Connection successful"; 
} 

 

2. Select the database to connect

Copy code The code is as follows:

mssql_select_db("dbname");

3. Execute query

Copy code The code is as follows:

$rs=mssql_query("selecttop1id,usernamefromtbname",$conn); 
 Or directly execute update, insert and other statements without assigning values ​​to the returned results 
mssql_query("updatetbnamesetusername='niunv'whereid=1"); 

 

4. Get the number of rows in the record set

Copy code The code is as follows:

echomssql_num_rows($rs);

5. Get the record set

Copy code The code is as follows:

if($row=mssql_fetch_array($rs)) 
{ 
 $id=$row[0];//Get the ID field value
$username=$row[1];//Get the username field value
} 

 

6. Get new Add the ID of the record
Set the id field to the IDENTITY field. After executing the insert statement, a @@IDENTITY global variable value will be generated. The query will be the ID of the last newly added record.

Copy code The code is as follows:

mssql_query("insertintotbname(username)values('nv')",$conn); 
$rs= mssql_query("select@@IDENTITYasid",$conn); 
if($row=mssql_fetch_array($rs)) 
{ 
echo$row[0]; 
} 

 

7. Release the record set

Copy code The code is as follows:

mssql_free_result($rs);

8. Close the connection

Copy code The code is as follows:

mssql_close($conn);

Note: It is easier to use PHP to operate MSSQL than to connect MYSQL to ASP. Therefore, when MSSQL and MYSQL need to coexist, it is easier and easier to use PHP to connect to MSSQL to operate MYSQL and MSSQL to coexist. If it is an ASP connection MYSQL needs to install a MYSQL driver. By default, Windows ODBC is not installed. Unfortunately...
1. Install at least mssql client on the web server
2. Open php.ini; extension=php_mssql Remove the semicolon in front of .dll
If necessary: you need to set extension_dir
3. It is recommended to use php<=4.0.9<=5.0.3. Currently, I have not successfully connected to 4.010 and 5.0.3
4. For database connection pagination, you can get the corresponding class from phpe.net
The following is a class I modified based on there

Copy code Code As follows:

/**
*mssql database connection class
**/
classSQL{
var$server;
var$userName;
var$passWord;
var$dataBase;
var$linkID=0;
var$queryResult;
var$lastInsertID;
var$pageNum=0;//分页用---共有几条数据
var$ER;
/**
*Constructor
**/
functionSQL($Server='',$UserName='',$PassWord='',$DataBase=''){
$this->server=$Server;
$this->userName=$UserName;
$this->passWord=$PassWord;
$this->dataBase=$DataBase;
}
/**
*Database connection
**/
functiondb_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";
return0;
}
if(!mssql_select_db($this->dataBase,$this->linkID)){
$this->ER="mssql_select_db($this->dataBase,$this->lastInsertID)error";
return0;
}
return$this->linkID;
}
/**public
*function:Checkthedatabase,ifexistthenselect
*exist:return1
*notexist:return0
*/
functionselectDatabase(){
if(mssql_select_db($this->dataBase))
return1;
else
return0;
}
/**
*Data operations
**/
functionquery($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.没有操作成功,queryerror！！";
return0;//****************For errors in php4.3.9 or above, use 1
}
return$this->queryResult;
}
/**
*Data acquisition
**/
functionfetch_array($result){
if($result!="")$this->queryResult=$result;
$rec=mssql_fetch_array($this->queryResult);
if(is_array($rec)){
return$rec;
}
//$this->ER="没有获取数据！";
return0;
}
/**public
*function:FreetheQueryResult
*successreturn1
*failed:return0
*/
functionfreeResult($result=""){
if($result!="")$this->queryResult=$result;
returnmssql_free_result($this->queryResult);
}
/**
* Get the number of affected rows
* Get the number of operated rows
**/
functionnum_rows($result=""){
if($result!=""){
$this->queryResult=$result;
$row=mssql_num_rows($this->queryResult);
return$row;
}
}
/**
*Get query results---Multiple
**/
functionresult_ar($str=''){
if(empty($str)){
return0;
}
$back=array();
$this->queryResult=$this->query($str);
while($row=$this->fetch_array($this->queryResult)){
$back[]=$row;
}
return$back;
}
/**
*Database information paging
*$Result database operation
*str==sql statement
*page==Which page
*showNum==How many pages to display
*/
functionpage($Str,$Page=0,$ShowNum=5){
$back=array();//返回数据
$maxNum=0;
if($Str==""){
$this->ER="没有数据";
return0;
}
$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;
}
/**
*Page html page number
*/
functionpage_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;
}
}//endclass
?>

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Implementation code for transferring variables between php and javascript_PHP tutorialNext article：Implementation code for transferring variables between php and javascript_PHP tutorial

Related articles

See more