Learn while learning PHP-(17) tutorial on using MySQLi Extension Library to operate database 2_PHP

Learn while learning PHP-(17) PHP use MySQLi Extension Library to operate Database 2. Learn while learning PHP-(17th) PHP use MySQLi Extension Library to operate the Database 2 from the day before yesterday, my computer did not know what happened, and the computer crashed for no reason when writing the program. Still not completely dead, while learning PHP-(17) PHP using MySQLi Extension Library to operate Database 2

Since the day before yesterday, my computer has no idea what's going on. when I write a program, it will crash for no reason. The task manager cannot be opened even if the mouse is still active. Last night, I checked the hard drive and there were six Bad Channels. I don't know why. But when I was playing, as long as I didn't open the compiler to write a program, nothing happened. This is not the case when you play games. Bored me.

IV. Mysqli Extension Library enhancement

The basic operations of the MySQLi Extension Library are similar, but the process orientation is transformed into object-oriented. However, since the mysql extension Library is enhanced, it must be strong. The following code is available:

1. execute SQL statements in batches

 Connect_error) {// die ($ mysqli-> connect_error); //} // $ sqls = insert into user (name, phone, address) values ('Zhang FE', 18899992222, 'China'); // $ sqls. = insert into user (name, phone, address) values ('Huang Feihong, 18899991111, 'China'); // $ sqls. = insert into user (name, phone, address) values ('wanggang', 18899993333, 'China'); // $ res = $ mysqli-> multi_query ($ sqls ); // if (! $ Res) {// echo execution failed; //} else {// echo OK; //} // $ mysqli-> close (); // you can use the insert delete update statement when executing dml statements in batches. However, you are advised not to insert the select statement // batch execute the dql statement (select) // 1. open mysqli object $ mysqli = new MySQLi (localhost, root, root, test); // 2. batch query $ sqls = select * from mr_user; $ sqls. = select * from user; // $ sqls. = desc user; // display the table structure // 3. processing result // if successful, there is at least one result set if ($ res = $ mysqli-> multi_query ($ sqls )) {do {// retrieve the first result set from mysqli connection $ result = $ Mysqli-> store_result (); // display the mysqli result object while ($ row = $ result-> fetch_row ()) {foreach ($ row as $ key => $ val) {echo $ val --;} echo ;} // release the resource $ result-> free (); if (! $ Mysqli-> more_results () {break ;} echo ************** new result set ************;} while ($ mysqli-> next_result ();} // 4. close resources?>

The databases used above are created by myself.

2. mysqli anti-injection mysqli Extension Library anti-injection is implemented through the pre-compilation mechanism. The so-called injection means that some computer hackers or attackers can obtain data or maliciously modify the data in your database when entering the form using your code defects. The mysql extension library can also be prevented by code optimization. Read a piece of code:

 Prepare ($ SQL); // bind the parameter $ name = array (Xiaoqian, Xiaobai, xiao); $ phone = array (18833332222,18744446666, 18899992222); $ address = array (ancient times, ancient, modern); // parameter binding ---->? Assign a value // The type and sequence must correspond to for ($ I = 0; $ I <3; $ I ++) {$ mysqli_stmt-> bind_param (sss, $ name [$ I], $ phone [$ I], $ address [$ I]); // Here, the three s indicate that the data type is string type. // execute the statement $ B = $ mysqli_stmt-> execute ();} if (! $ B) {die (operation failed. $ mysqli_stmt-> error);} else {echo operation successful;} // release resource $ mysqli-> close ();?>
 User id, name, address // 1. create a mysqli object $ mysqli = new MySQLi (localhost, root, root, test); // 2. create a precompiled object $ SQL = select id, name, address from user where id> ?; $ Mysqli_stmt = $ mysqli-> prepare ($ SQL); $ I = 8; // bind the parameter $ mysqli_stmt-> bind_param (I, $ I ); // because an object is returned this time, you need to bind the result set $ mysqli_stmt-> bind_result ($ id, $ name, $ address ); // execute $ mysqli_stmt-> execute (); // Retrieve the bound result set while ($ mysqli_stmt-> fetch ()) {echo -- $ id -- $ name -- $ address --;} // If you want to execute a query similar to the preceding one, then you do not need to bind the result set // release the resource $ mysqli_stmt-> free_result (); // close the pre-compiled command $ mysqli_stmt-> close (); // close the connection $ mysqli-> close ();?>

3. transaction processing transactions are used to ensure data consistency. they are composed of a group of related dml statements. the dml statements in this group are either all succeeded or all failed. In general, transactions can solve some misoperations or improper operations. For example, online transfers are typically handled by transactions to ensure data consistency. If I want to transfer the money to you, but you haven't received it for system reasons, you need to use transaction rollback to cancel the operation I just performed, I cannot lose your money without increasing it.

 Connect_error) {die ($ mysqli-> connect_error);} // Set the submission to false $ mysqli-> autocommit (false ); // enable or disable the automatic command for this database connection to submit the transaction mode. here, the setting does not enable automatic commit (false) // This is equivalent to a transparent save point. The current situation is recorded. $ Sql1 = update account set balance = balance + 100 where id = 1; $ sql2 = update account set balance = balance-100 where id = 2; $ res1 = $ mysqli-> query ($ sql1); $ res2 = $ mysqli-> query ($ sql2); if (! $ Res1 |! $ Res2) {// roll back the transaction $ mysqli-> rollback (); // roll back to the Save point echo fail;} else {// submit $ mysqli-> commit (); // Here is the true commit. once submitted, there is no chance to roll back. Echo success ;}?>

Use Transaction Details: You can also use transactions in the mysql console. The procedure is as follows:
1. start a transaction
Start transaction
2. do the save point. if not, it will be used as the save point from the beginning of the transaction by default.
Savepoint
3. operations ......
4. roll back and submit
4.1 submit the ticket if no problem is found
Commit
4.2 roll back if you think there is a problem
Rollback to save point
(Once submitted using commit, all save points are cleared and cannot be rolled back)
Acid properties of transactions
* Atomicity: a transaction is an inseparable unit of work. operations in a transaction either occur or do not occur.
* Consistency: the transaction must change the database from a consistent state to another consistent state.
* Durability: when multiple users access the database concurrently, the database starts transactions for each user and cannot be disturbed by the operation data of other transactions, multiple concurrent transactions must be isolated from each other.
* Isolation: durability means that once a transaction is committed, its changes to the data in the database are permanent. in the future, even if the database fails, it should not have any impact on it.

MySQL-(17th) PHP used MySQLi Extension Library to operate the database. 2 my computer had no idea what happened since the day before yesterday, and it crashed for no reason when I was writing a program. Not completely dead ,...