Learn php-(17) PHP using MYSQLI Extension Library Operations database 2

From the day before yesterday, my computer did not know how, write the program when no reason to crash. is not completely dead, the mouse is also active, that is, the click does not work, also can not open Task Manager. Check the hard drive last night, there are six bad lanes, do not know whether this is the reason. But when I'm playing, I don't have to open the compiler to write a program, nothing. Playing the game does not come back to that situation. I'm so tired of doing it.

Iv. mysqli Expansion Library Enhancements

The mysqli extension library looks no different on the underlying operation, but transforms from a face-oriented process into an object-oriented. But since it's an enhancement to the MySQL expansion library, there must be a strong place. The following code is available:

1. Batch execution of SQL statements

<?php//mysqli enhancements, batch execution of SQL statements//batch execution of DML statements (increased pruning)//use of Mysqli::multi_query ($SQLS) method//$sqls = "$sql 1; $sql 2;         ...";     1. Get Mysqli object//$mysqli =new mysqli ("localhost", "root", "root", "test");     if ($mysqli->connect_error) {//Die ($mysqli->connect_error);     }//$sqls = "INSERT into user (name,phone,address) VALUES (' Zhang Fei ', 18899992222, ' China ');";     $sqls. = "INSERT into user (name,phone,address) VALUES (' Yellow feihong ', 18899991111, ' China ');";     $sqls. = "INSERT into user (name,phone,address) VALUES (' Wang Gang ', 18899993333, ' China ');";     $res = $mysqli->multi_query ($SQLS);     if (! $res) {//echo "failed to execute";     }else{//echo "OK";     }//$mysqli->close (); Batch execution of DML statements can be mixed: Insert Delete UPDATE statement, but it is best not to insert the SELECT statement//batch Execution DQL statement (SELECT)//1. Open Mysqli Object $mysqli =new My     SQLi ("localhost", "root", "root", "test");     2. Batch query $sqls = "SELECT * from Mr_user;";     $sqls. = "SELECT * from user;";      $sqls. = "desc user";//display the structure of the table   3. Processing results//If successful, there is at least one result set if ($res = $mysqli->multi_query ($sqls)) {do{//Remove the first knot from the mysqli connection               Fruit set $result = $mysqli->store_result ();                         Displays the Mysqli result object while ($row = $result->fetch_row ()) {foreach ($row as $key = = $val) {                    echo "$val--";               } echo "<br>";               }//After using the first result set, the resources should be released in a timely manner $result->free ();               if (! $mysqli->more_results ()) {break;          } echo "<br>************** new result set ************<br>";         }while ($mysqli->next_result ()); }//4. Close Resource?>

The database I used above was built on my own.

2, mysqli anti-injectionMYSQLI Extension Library Anti-injection is done through a pre-compilation mechanism. The so-called injection is some computer hackers or attackers using your code defects in filling out the form can be obtained in your database data or malicious modification. In the MySQL extension library, it can also be prevented by code optimizations. Look at the code:

<?php//Pre-compilation mechanism, add three user//1. Create a Mysqli object $mysqli = new mysqli ("localhost", "root", "root", "test"); 2. Create precompiled Object $sql = "INSERT into user (name,phone,address) VALUES (?,?,?)";     /question mark is a placeholder, and later as long as the data is replaced with OK $mysqli _stmt = $mysqli->prepare ($sql);     Binding Parameters $name = Array ("Xiao Qian", "small white", "small black");     $phone = Array ("18833332222", "18744446666", "18899992222");     $address = Array ("Ancient", "ancient", "modern"); Parameter binding----> give? Assignment//The type and order here correspond to for ($i =0; $i <3; $i + +) {$mysqli _stmt->bind_param ("SSS", $name [$i], $phone [$i], $address [$i]);     Here three S is the representation that the data type is a string type//execution statement $b = $mysqli _stmt->execute ();     } if (! $b) {die ("operation failed". $mysqli _stmt->error);     }else{Echo "Operation succeeded"; }//Release resources $mysqli->close ();? ><?php//Pre-compile mechanism, query from database//Use preprocessing method, query all id>8 user's id,name, address//1. Create a Mysqli object $mysqli = new MYSQ     Li ("localhost", "root", "root", "test");     2. Create precompiled Object $sql = "Select id,name,address from user where id>?"; $mysqli_stmt = $mysqli->prepare ($sql);     $i = 8;     Binding parameters $mysqli _stmt->bind_param ("i", $i);     Due to the return object this time, we need to bind the result set $mysqli _stmt->bind_result ($id, $name, $address);     Execution $mysqli _stmt->execute ();     Take out the bound result set while ($mysqli _stmt->fetch ()) {echo "<br>--$id--$name--$address--";     }//If you want to perform a query like the one above just $i change, then there is no need to bind the result set//release resources $mysqli _stmt->free_result ();     Close the precompiled Directive $mysqli _stmt->close (); Close connection $mysqli->close ();? >

3, transaction processingTransactions are used to guarantee data consistency, which consists of a set of related DML statements that either all succeed or fail altogether. The popular point is that transactions can solve some misoperation, or improper operation. such as: online transfer is typical to use transactions to handle, to ensure the consistency of data. If I want to transfer to you, my money beat, but because of system reasons you did not receive, then need to use the transaction rollback, undo I just the operation, can not let my money less your money does not increase.

<?php     $mysqli = new mysqli ("localhost", "root", "root", "test");     if ($mysqli->connect_error) {die          ($mysqli->connect_error);     }     Set the commit to False     $mysqli->autocommit (FALSE);//Turn on or off the automatic command commit TRANSACTION mode for this database connection, where it is not automatically committed (false)     // This is the equivalent of making a transparent point of preservation. Will record the current situation.     $sql 1 = "Update account set balance=balance+100 where id = 1";     $sql 2 = "Update account set balance=balance-100 where id = 2";     $res 1 = $mysqli->query ($sql 1);     $res 2 = $mysqli->query ($sql 2);         if (! $res 1| |! $res 2) {          //ROLLBACK TRANSACTION          $mysqli->rollback ();//rollback to save point          echo "fail";     } else{          //Submit          $mysqli->commit ();  Here is the real commit, once the commit has no chance to roll back.          echo "Success";     }? >

Use transaction details: The MySQL console can also use transactions to operate. The steps are as follows:
1. Open a transaction
Start transaction
2. Do a savepoint, default from the start of the transaction as the savepoint if not done
SavePoint Save Point Name
3. Operation ...
4. Can roll back, can submit
4.1 If there is no problem, submit
Commit
4.2 If you feel a problem, roll back.
Rollback to save point
(once commit is used, the savepoint is cleared and cannot be rolled back.)
ACID Properties of transactions
* Atomicity: atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.
* Consistency: The transaction must transform the database from one consistent state to another consistent state
* Persistence: Transaction isolation is when multiple users access the database concurrently, the database opens a transaction for each user, cannot be disturbed by the operation data of other things, and the multiple concurrent transactions are isolated from each other.
* Isolation: Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.


Here I write my own mysqli the content of the extension library is too small. Still need to take a good look at the book.

Learn php-(17) PHP using MYSQLI Extension Library Operations database 2