Summary of php connection to mssql

Summary of php connection 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. 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. obtain 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. obtain the ID of the new record and set the id field to the IDENTITY Field. after the insert statement is executed, a @ IDENTITY global variable value is generated, the query result shows 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 // More 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 the mssql client on the web server. open php. ini remove the semicolon before; extension = php_mssql.dll if necessary: extension_dir 3 needs to be formulated. 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. the connection page of the database can be obtained on phpe.net. Below is a class that I modified based on it. /** * 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 = "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; } } // End class ?>