Php connection to mssql is a common practice for many of my friends. However, this article is still a bit nagging, mainly to share the experience related to database connection. If you are interested, do not miss it, maybe this article is helpful to you.
Php connection to mssql is a common practice for many of my friends. However, this article is still a bit nagging, mainly to share the experience related to database connection. If you are interested, do not miss it, maybe this article is helpful to you.
To connect PHP to MSSQL, the system needs to install MSSQL and PHP. In the configuration of PHP. ini
; Extension = php_mssql.dll; remove
1. Connect to MSSQL
The Code is as follows:
$ Conn = mssql_connect ("Instance name or server IP Address", "username", "password ");
// Test the connection
If ($ conn)
{
Echo "connection successful ";
}
2. Select the database to connect
The Code is as follows:
Mssql_select_db ("dbname ");
3. Execute the query
The Code is as follows:
$ Rs = mssql_query ("selecttop1id, usernamefromtbname", $ conn );
Or directly execute the update, insert, and other statements without assigning values to the returned results.
Mssql_query ("updatetbnamesetusername = 'niunv' whereid = 1 ");
4. Get the number of Record Sets
The Code is as follows:
Echomssql_num_rows ($ rs );
5. Obtain the record set
The Code is as follows:
If ($ row = mssql_fetch_array ($ rs ))
{
$ Id = $ row [0]; // obtain the ID field value
$ Username = $ row [1]; // obtain the value of the username field.
}
6. Get the ID of the new record
Set the id field to the IDENTITY field. After the insert statement is executed, a @ IDENTITY global variable value is generated. The queried result is the ID of the last new record.
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 record set
The Code is as follows:
Mssql_free_result ($ rs );
8. Close the connection
The Code is as follows:
Mssql_close ($ conn );
Note: using PHP to operate MSSQL is easier than connecting to MYSQL in ASP. Therefore, when MSSQL and MYSQL are required to coexist, it is easier to use PHP to connect MSSQL to operate the coexistence of MYSQL and MSSQL. if ASP is used to connect to MYSQL, you need to install a MYSQL driver. By default, ODBC in windows is not installed. Sorry...
1. Install at least mssql client on the web Server
2. Open php. ini and remove the semicolon before extension = php_mssql.dll.
Required: extension_dir
3. We recommend that you use php <= 4.0.9 <= 5.0.3. Currently, I have not successfully connected php 4.010 or 5.0.3.
4. You can go to phpe.net to obtain the corresponding class on the database connection page.
The following is a class that I modified based on it.
The Code is 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; // use for paging --- a total of several data records
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 = "the database is not connected !! ";
}
$ This-> queryResult = mssql_query ($ Str );
// $ This-> queryResult = mssql_query ($ Str, $ this-> linkID );
If (! $ This-> queryResult ){
$ This-> ER = "$ Str. No operation is successful. queryerror !! ";
Return0; // ****************** for php4.3.9 or a later version of Error 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 = "No data is obtained! ";
Return0;
}
/** Public
* Function: FreetheQueryResult
* Successreturn1
* Failed: return0
*/
FunctionfreeResult ($ result = ""){
If ($ result! = "") $ This-> queryResult = $ result;
Returnmssql_free_result ($ this-> queryResult );
}
/**
* Obtain the number of affected rows.
* Get the number of rows that have been operated
**/
Functionnum_rows ($ result = ""){
If ($ result! = ""){
$ This-> queryResult = $ result;
$ Row = mssql_num_rows ($ this-> queryResult );
Return $ row;
}
}
/**
* Retrieve 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 operations
* Str = SQL statement
* Page = page number
* ShowNum = display pages
*/
Functionpage ($ Str, $ Page = 0, $ ShowNum = 5 ){
$ Back = array (); // return data
$ MaxNum = 0;
If ($ Str = ""){
$ This-> ER = "no data ";
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 number of the html page
*/
Functionpage_html ($ DataNum = 0, $ Page = 1, $ ShowNum = 3, $ web, $ Post = ''){
If ($ DataNum = 0 ){
$ Back = "no data to be queried ";
} Else {
If ($ ShowNum <= 0 ){
$ ShowNum = 3;
}
If ($ Page <= 0 ){
$ Page = 1;
}
If (empty ($ web )){
$ Web = "#";
}
$ PageNum = ceil ($ DataNum/$ ShowNum );
If ($ Page <= 1 ){
$ Top = "homepage <";
} 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 ";
}
$ Back = < Total $ pageNum pages
Page $ Page/$ pageNum Page $ top $ upPage $ downPage $ foot
EOT;
}
Return $ back;
}
} // Endclass
?>
