Home > Article > Backend Development > Some relevant experience and summary of php connecting to mssql_PHP tutorial
Some relevant experience and summary of php connecting to mssql_PHP tutorial
- WBOYOriginal
- 2016-07-14 10:08:38870browse
In order to allow PHP to connect to MSSQL, the system needs to install MSSQL and PHP, and in the configuration in PHP.ini, set
;Remove
in front of extension=php_mssql.dll
1. Connect to MSSQL
$conn=mssql_connect("Instance name or server IP", "Username", "Password");
//Test connection
if($conn)
{
echo "Connection successful";
}
2. Select the database to connect
mssql_select_db("dbname");
3. Execute query
$rs = mssql_query("select top 1 id,username from tbname",$conn);
Or directly execute update, insert and other statements without assigning a value to the return result
mssql_query("update tbname set username='niunv' where id=1");
4. Get the number of rows in the record set
echo mssql_num_rows($rs);
5. Get the record set
if($row=mssql_fetch_array($rs))
{
$id = $row[0];//Get the ID field value
$username = $row[1];//Get the username field value
}
6. Get the ID of the new record
Set the id field to the IDENTITY field. After executing the insert statement, an @@IDENTITY global variable value will be generated. The query will be the ID of the last newly added record.
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. Release the record set //More http://www.52mvc.com
mssql_free_result($rs);
8. Close the connection
mssql_close($conn);
Note: It is easier to use PHP to operate MSSQL than to connect to MYSQL in 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 ASP to connect to MYSQL, it needs to be installed. A MYSQL driver, the default windows ODBC is not installed, unfortunately...
1. Install at least mssql client on the web server
2. Open php.ini and remove the semicolon in front of ;extension=php_mssql.dll
If necessary: extension_dir needs to be formulated
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. The connection pagination of the database can be obtained from phpe.net to obtain the corresponding class
The following is a class I modified based on there
/**
*mssql database connection class
**/
class SQL{
var $server;
var $userName;
var $passWord;
var $dataBase;
var $linkID = 0;
var $queryResult;
var $lastInsertID;
var $pageNum = 0;//For paging---there are several pieces of data in total
var $ER;
/**
*Constructor
**/
function SQL($Server='',$UserName='',$PassWord='',$DataBase=''){
$this->server = $Server;
$this->userName = $UserName;
$this->passWord = $PassWord;
$this->dataBase = $DataBase;
}
/**
*Database connection
**/
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;
}
/**
*Data operation
**/
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;//****************For errors with PHP 4.3.9 or above, use 1
}
return $this->queryResult;
}
/**
*Data acquisition
**/
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);
}
/**
*Get the number of affected rows
*Get the number of rows operated
**/
function num_rows($result=""){
if ($result != "") {
$this->queryResult = $result;
$row = mssql_num_rows($this->queryResult);
return $row;
}
}
/**
*Get query results---Multiple
**/
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;
}
/**
*Database information paging
*$Result Database operation
*str ==sql statement
*page ==Which page
*showNum ==Show several pages
*/
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;
}
/**
*Page html page number
*/
function page_html($DataNum=0,$Page=1,$ShowNum=3,$web,$Post=''){
if ($DataNum == 0) {
$back = "No data to query";
}else {
if ($ShowNum<=0) {
$ShowNum = 3;
}
if ($Page<=0) {
$Page = 1;
}
if (empty($web)) {
$web = "#";
}
$pageNum = ceil($DataNum/$ShowNum);
if ($Page <= 1) {
$top = "Home<<";
}else {
$top = "Homepage<< ";
}
if ($Page !==1) {
$upPage = "Previous page ";
}else {
$upPage = "Previous page";
}
if ($Page < $pageNum) {
$downPage = "Next page ";
}else {
$downPage = "Next page";
}
if ($Page == $pageNum) {
$foot = ">>Last Page";
}else {
$foot = " >>Last page< ;/a>";
}
$back = <<
Total $pageNum pages
Page $Page/$pageNum $top $upPage $downPage $foot
EOT;
}
return $back;
}
}//end class
?>
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：PHP implementation of singleton mode_PHP tutorialNext article：PHP implementation of singleton mode_PHP tutorial