PHPmysqli extension Library (Object-Oriented/database operation encapsulation/transaction control/pre-compilation)

1. Differences from mysql extension libraries: (1) higher security and stability (2) providing object-oriented and process-oriented styles 2. php. extensionphp_mysqli.dll in ini unseal 3. Object-oriented: Query List 1? Php23mysqli operation data (Object-Oriented style) 45 #1. Create a Mysql

1. Differences from mysql extension libraries: (1. Higher security and stability (2. Providing object-oriented and process-oriented styles 2. php. extension = php_mysqli.dll in ini unseal 3. Object-oriented: Query List 1? Php 2 3 // mysqli operation data (Object-Oriented style) 4 5 #1. Create a Mysql

1. mysqlExtensionDatabase differences:

(1. High Security and Stability

(2 providesOrientedObjectAndOrientedTwo process styles

2. in php. ini, extend = php_mysqli.dll to unseal

3,OrientedObject: Query list

1
 OperationData (OrientedObjectStyle) 4 5 #1. Create MysqlObject6 7 $ mysqli = new MySQLi ("127.0.0.1", "root", "daomul", "test"); 8 if (! $ Mysqli) 9 {10 die ("connection failed! ". $ Mysqli-> connect_error); 11} 12 13 #2,OperationDatabase14 15 $ SQL = "select * from user1"; 16 $ res = $ mysqli-> query ($ SQL ); 17 #3. processing result 18 19 while ($ row = $ res-> fetch_row () 20 {21 foreach ($ row as $ key => $ val) 22 {23 echo "-- $ val"; 24} 25 echo"
"; 26} 27 #4. close resource 28 $ res-> free (); // release memory 29 $ mysqli-> close (); // close connection 30 31?>

4,OrientedObject:EncapsulationClass implementation

4.1 Sqliconnect. class. php

1
 Mysqli = new MySQLi (self ::$ host, self ::$ root, self ::$ password, self ::$ db); 14 if (! $ This-> mysqli) 15 {16 die ("DatabaseConnection Failed! ". $ This-> mysqli-> connect_error); 17} 18 19 $ this-> mysqli-> query (" set names utf8 "); 20} 21 22 // queryOperation23 public function excute_dql ($ SQL) 24 {25 $ res = $ this-> mysqli-> query ($ SQL) or die ("Data query failed ". $ this-> mysqli-> error); 26 return $ res; 27 28} 29 30 // add, delete, and modifyOperation31 public function excute_dml ($ SQL) 32 {33 $ res = $ this-> mysqli-> query ($ SQL) or die ("DataOperationFailed ". $ this-> mysqli-> error); 34 if (! $ Res) 35 {36 echo "DataOperationFailed "; 37} 38 else39 {40 if ($ this-> mysqli-> affected_rows> 0) 41 {42 echo"OperationSuccessful! "; 43} 44 else45 {46 echo" 0 rows of data are affected! "; 47} 48} 49} 50 51} 52?>

4.2 call page startsqli. php

1
 OperationData (OrientedObjectStyle) 4 5 6 require_once "Sqliconnect. class. php "; 7 8 $ Sqliconnect = new Sqliconnect (); 9 10 // $ SQL =" insert into user1 (name, password, email, age) values ('did ', md5 ('gg '), 'sd @ sohu.com', 23) "; 11 // $ SQL =" delete from user1 where id = 11 "; 12 // $ res = $ Sqliconnect-> excute_dml ($ SQL); 13 14 $ SQL = "select name from user1 ;"; 15 $ res = $ Sqliconnect-> excute_dql ($ SQL); 16 while ($ row = $) 17 18 $ res-> free (); 19?>

5. Execute multiple entries simultaneouslyDatabaseStatement multiQuery. php

1
 OperationData (OrientedObjectStyle) 4 5 #1. Create MysqlObject6 7 $ mysqli = new MySQLi ("127.0.0.1", "root", "daomul", "test"); 8 if (! $ Mysqli) 9 {10 die ("connection failed! ". $ Mysqli-> connect_error); 11} 12 13 #2,OperationDatabase14 15 $ sqls = "select * from user1;"; 16 $ sqls. = "select * from user1"; 17 18 #3. processing result 19 20 if ($ res = $ mysqli-> multi_query ($ sqls) 21 {22 echo "211 "; 23 do 24 {25 // extract the first result set from mysqli consecutively 26 $ result = $ mysqli-> store_result (); 27 28 // display mysqli resultObject29 while ($ row = $ result-> fetch_row () 30 {31 foreach ($ row as $ key => $ val) 32 {33 echo "-- $ val "; 34} 35 echo"
"; 36} 37 38 $ result-> free (); // release the current result set in time, and enter the next result set 39 40 // determine whether there is a next result set 41 if (! $ Mysqli-> more_results () 42 {43 break; 44} 45 echo"
**************"; 46 47} while ($ mysqli-> next_result (); 48} 49 50 #4. close resource 51 $ mysqli-> close (); // close the connection 52 53 54?>

6,TransactionsControl

1
 OperationData (OrientedObjectStyle) 4 5 6 //Database: Create table account (id int primary key, balance float); 7 8 $ mysqli = new MySQLi ("127.0.0.1", "root", "daomul", "test "); 9 if (! $ Mysqli) 10 {11 die ("DatabaseConnection Failed! ". $ Mysqli-> connect_error); 12} 13 // set the submission to false14 $ mysqli-> autocommit (false ); 15 16 $ sql1 = "update account set balance = balance + 1 where id = 1 ;"; // correct statement 17 $ sql2 = "update accounterror2 set balance = balance-1 where id = 2 "; // wrong statement 18 19 $ res1 = $ mysqli-> query ($ sql1); 20 $ res2 = $ mysqli-> query ($ sql2); 21 22 if (! $ Res1 |! $ Res2) 23 {24 // rollback: if one of them is unsuccessful, rollback will not be submitted. 25 echo "Incorrect. rollback. Please submit it again! "; 26 $ mysqli-> rollback (); // die ("OperationFailed! ". $ Mysqli-> error); 27} 28 else29 {30 // if all requests are successful, 31 echo is submitted." All requests are submitted successfully! "; 32 $ mysqli-> commit (); 33} 34 35 $ mysqli-> close (); 36/* 37 1, start transaction; EnabledTransactions38 2. svaepoint a; save point 39; ExecuteOperation1; 40 4. svaepoint B; 41 5. ExecutionOperation2; 42... 43 6. rollback to a/B; rollback or commit 44 7. commit 45 46TransactionsControlFeatures acid atomicity/Consistency/isolation/durability 47 */48?>

7. Preprocessing Technology

Mainly in connection andCompileThe process is simplified, and SQL can prevent injection.

7.1 pre-CompileInsert multiple data

1
 CompileDemonstration 4 5 #1. Create mysqliObject6 $ mysqli = new MySQLi ("127.0.0.1", "root", "daomul", "test"); 7 if (! $ Mysqli) 8 {9 die ("DatabaseConnection Failed! ". $ Mysqli-> connect_error); 10} 11 12 #2. Create a pre-CompileObject13 $ SQL = "insert into user1 (name, password, email, age) values (?,?,?,?); "; // Do not assign values for the moment. Use a question mark instead of 14 $ stmt = $ mysqli-> prepare ($ SQL) or die ($ mysqli-> error ); 15 16 /* code start *********************************/17 #3, binding parameter 18 $ name = 'xiaoming 5 '; 19 $ password = '34f'; 20 $ email = 'ssd @ qq.com '; 21 $ age = '1 '; 22 23 #4. parameter assignment (the first parameter refers to the abbreviated type of the parameter, string-s, int-I, double-d, bool-b24 $ stmt-> bind_param ("sssi ", $ name, $ password, $ email, $ age); 25 26 #5. Run the code (return boolean type) 27 $ flag = $ stmt-> execute (); 28 29/********************************* repeated execution is required code end ************************************/ 30 31 #6. Result and release 32 33 if (! $ Flag) 34 {35 die ("OperationFailed ". $ stmt-> error); 36} 37 else38 {39 echo"OperationSuccessful! "; 40} 41 42 $ mysqli-> close (); 43 44 45?>

7.2 pre-CompileQuery multiple data items

1
 CompileDemonstration 4 5 #1. Create mysqliObject6 $ mysqli = new MySQLi ("127.0.0.1", "root", "daomul", "test"); 7 if (! $ Mysqli) 8 {9 die ("DatabaseConnection Failed! ". $ Mysqli-> connect_error ); 10} 11 12/******************************** repeated execution required code start ********************************/13 14 #2. Create a pre-CompileObject15 $ SQL = "select id, name, email from user1 where id> ?; "; // Id, name, email, and bind_result () of the subsequent result set correspond to 16 $ stmt = $ mysqli-> prepare ($ SQL) or die ($ mysqli-> error); 17 18 #3. Bind the parameter 19 $ id = 5; 20 21 #4. assign a value to the parameter (the first parameter refers to the abbreviation of the parameter type, string-s, int-I, double-d, bool-b22 $ stmt-> bind_param ("I", $ id ); // bind_result ($ id, $ name, $ email) binding parameter 23 $ stmt-> bind_result ($ id, $ name, $ email); // bind result set 24 25 #5. Execute Code (return boolean type) 26 $ stmt-> execute (); 27 28 #6. 29 while ($ stmt-> fetch () 30 {31 echo"
$ Id -- $ name -- $ email "; 32} 33 34/******************************** repeated execution code end ********************************/35 36 #7. Result and release 37 38 // release result 39 $ stmt-> free_result (); 40 // close the pre-CompileStatement 41 $ stmt-> close (); 42 // closeDatabaseConnection 43 $ mysqli-> close (); 44 45 46?>

8. Other functions

(1) obtain the number of rows and columns num_rows field_count

(2) Obtain a column of the result set: the header is

$ Result = $ mysqli-> query ();

$ Result-> fetch_field ();

(3. Retrieve Data

$ Row = $ result-> fetch_row (); // obtain each row of data

Then retrieve each data through foreach ($ row as $ val) {}