Summary of the methods for connecting php to mssql

Summary of the methods for connecting php to mssql

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
$ 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
Mssql_select_db ("dbname ");

3. Execute the query
$ Rs = mssql_query ("select top 1 id, username from tbname", $ conn );
Or directly execute the update, insert, and other statements without assigning values to the returned results.
Mssql_query ("update tbname set username = 'niunv' where id = 1 ");

4. Get the number of Record Sets
Echo mssql_num_rows ($ rs );

5. Obtain the record set
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.
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 record set
Mssql_free_result ($ rs );

8. Close the connection
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.
<? Php
/**
* Mssql database connection class
**/
Class SQL {

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
**/
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 Operations
**/
Function query ($ 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. query error !! ";
Return 0; // ***************** for php 4.3.9 or a later version of Error 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 = "No data is obtained! ";
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 );
}

/**
* Obtain the number of affected rows.
* Get the number of rows that have been operated
**/
Function num_rows ($ result = ""){
If ($ result! = ""){
$ This-> queryResult = $ result;
$ Row = mssql_num_rows ($ this-> queryResult );
Return $ row;
}
}

/**
* Retrieve 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 operations
* Str = SQL statement
* Page = page number
* ShowNum = display pages
*/
Function page ($ Str, $ Page = 0, $ ShowNum = 5 ){
$ Back = array (); // return data
$ MaxNum = 0;
If ($ Str = ""){
$ This-> ER = "no data ";
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 number of the html page
*/
Function page_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 = "<a href = '". $ web ."? Page = 0 & ". $ Post." 'target = '_ self'> homepage </a> ";
}
If ($ Page! = 1 ){
$ UpPage = "<a href = '". $ web ."? Page = ". ($ Page-1)." & ". $ Post." 'target = '_ self'> previous page </a> ";
} Else {
$ UpPage = "Previous Page ";
}
If ($ Page <$ pageNum ){
$ DownPage = "<a href = '". $ web ."? Page = ". ($ Page + 1)." & ". $ Post." 'target = '_ self'> next page </a> ";
} Else {
$ DownPage = "next page ";
}
If ($ Page = $ pageNum ){
$ Foot = "> last page ";
} Else {
$ Foot = "<a href = '". $ web ."? Page = ". $ pageNum." & ". $ Post." 'target = '_ self' >>> last page </a> ";
}

$ Back = <EOT

Total $ pageNum pages
Page $ Page/$ pageNum Page $ top $ upPage $ downPage $ foot
EOT;
}
Return $ back;
}
} // End class
?>