Learn PHP while memorizing - (17) PHP uses the MySQLi extension library to operate the database 2

Since the day before yesterday, I don’t know what happened to my computer. It crashed for no reason when I was writing a program. It's not completely dead, the mouse can still move, but the click doesn't work, and the task manager can't be opened. I checked the hard drive last night and found six bad sectors. I don't know if this is the reason. But when I was playing, as long as I didn't open the compiler and write the program, nothing would happen. That won't happen again when playing games. It annoys me so much.

4. Mysqli extension library enhancement

The basic operation of the MySQLi extension library seems to be the same, but it is converted from process-oriented to object-oriented. But since it is an enhancement of the mysql extension library, it must have strong points. There is the following code:

1. Execute sql statements in batches

<!--?php
     //mysqli 的增强，批量执行sql语句

     //批量执行dml语句(增删改)
     //使用了mysqli::multi_query($sqls)方法
     //$sqls = $sql1;$sql2;...;
    
     //1.得到mysqli对象
     //$mysqli=new MySQLi(localhost,root,root,test);
     //if($mysqli--->connect_error){
     //     die($mysqli->connect_error);
     //}
     //$sqls = insert into user(name,phone,address)values(&#39;张飞&#39;,18899992222,&#39;中国&#39;);;
     //$sqls .= insert into user(name,phone,address)values(&#39;黄飞鸿&#39;,18899991111,&#39;中国&#39;);;
     //$sqls .= insert into user(name,phone,address)values(&#39;王刚&#39;,18899993333,&#39;中国&#39;);;

     //$res = $mysqli->multi_query($sqls);
     //if(!$res){
     //     echo 执行失败;
     //}else{
     //     echo ok;
     //}
     //$mysqli->close();
     //批量执行dml语句的时候可以混合使用：insert delete update语句，但是最好不要插入select语句

     //批量执行dql语句（select）
     //1.打开mysqli对象
     $mysqli=new MySQLi(localhost,root,root,test);
     //2.批量查询
     $sqls = select * from mr_user;;
     $sqls.=select * from user;;
     //$sqls.=desc user;//显示表的结构
    
     //3.处理结果
     //如果成功，则至少有一个结果集
     if($res=$mysqli->multi_query($sqls)){
          do{
               //从mysqli连接取出第一个结果集
               $result = $mysqli->store_result();
               //显示mysqli result对象
               while($row=$result->fetch_row()){
                    foreach($row as $key=>$val){
                         echo $val--;
                    }
                    echo 
;
               }
               //使用完第一个结果集后应该及时释放资源
               $result->free();
               if(!$mysqli->more_results()){
                    break;
               }
               echo 
**************新的结果集************
;

          }while($mysqli->next_result());
    
     }
     //4.关闭资源



?>

Copy after login

The database used above was built by myself.

2. mysqli anti-injection

The mysqli extension library prevents injection through the precompilation mechanism. The so-called injection means that some computer hackers or attackers can use the flaws in your code to obtain the data in your database or make malicious modifications when filling out the form. In the mysql extension library, it can also be prevented through code optimization. Look at a piece of code:

<!--?php

     //预编译机制,添加三个用户
     //1.创建一个mysqli对象
     $mysqli = new MySQLi(localhost,root,root,test);

     //2.创建预编译对象
     $sql = insert into user (name,phone,address) values(?,?,?);//问号是占位符，以后只要用数据替换就ok
     $mysqli_stmt = $mysqli--->prepare($sql);

     //绑定参数
     $name = array(小倩,小白,小黑);
     $phone = array(18833332222,18744446666,18899992222);
     $address = array(古代,古代,现代);
     //参数绑定---->给？赋值
     //这里类型和顺序都要对应
     for($i=0;$i<3;$i++){
     $mysqli_stmt->bind_param(sss,$name[$i],$phone[$i],$address[$i]);//这里三个s是代表数据类型是字符串类型

     //执行语句

     $b = $mysqli_stmt->execute();
     }



     if(!$b){
          die(操作失败.$mysqli_stmt->error);
     }else{
          echo 操作成功;
     }

     //释放资源
     $mysqli->close();


?>


<!--?php

     //预编译机制,从数据库查询
     //使用预处理的方法，查询所有Id-->8的用户的id,name ,address
     //1.创建一个mysqli对象
     $mysqli = new MySQLi(localhost,root,root,test);

     //2.创建预编译对象
     $sql = select id,name,address from user where id>?;

     $mysqli_stmt = $mysqli->prepare($sql);

     $i=8;
     //绑定参数
     $mysqli_stmt->bind_param(i,$i);
     //由于这次有返回对象了，所以需要绑定结果集
     $mysqli_stmt->bind_result($id,$name,$address);
     //执行
     $mysqli_stmt->execute();

     //取出绑定的结果集
     while($mysqli_stmt->fetch()){
          echo 
--$id--$name--$address--;
     }
    

     //如果想再执行一次类似上面的只是$i改变的查询，那么就无须绑定结果集

     //释放资源
     $mysqli_stmt->free_result();
     //关闭预编译指令
     $mysqli_stmt->close();
     //关闭连接
     $mysqli->close();

?>

Copy after login

3. Transaction processing

Transaction is used to ensure data consistency. It consists of a group of related DML statements. The DML statements of this group either all succeed or all fail. In layman's terms, transactions can solve some misoperations, or improper operations. For example, online transfers are typically processed using transactions to ensure data consistency. If I want to transfer money to you, and my money is transferred, but you don't receive it due to system reasons, then you need to use transaction rollback to undo the operation I just made. I can't let my money decrease and your money not increase.

<!--?php

     $mysqli = new MySQLi(localhost,root,root,test);
     if($mysqli--->connect_error){
          die($mysqli->connect_error);
     }

     //将提交设为false

     $mysqli->autocommit(false);//打开或关闭本次数据库连接的自动命令提交事务模式 ，这里设置不要自动提交（false）
     //这里相当于做了一个透明的保存点。会把当前情况记录下来。

     $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){
          //回滚事务
          $mysqli->rollback();//回滚到保存点
          echo fail;
     }else{
          //提交
          $mysqli->commit();  //这里是真正的提交，一旦提交没有机会回滚。
          echo success;
     }

?>

Copy after login

Details of using transactions: You can also use transactions to operate in the mysql console. The specific steps are as follows:
1. Start a transaction
start transaction
2. Make a save point. If not, it will default to the start of the transaction as the save point
savepoint savepoint name
3. Operation...
4. Can be rolled back and submitted
4.1 If there are no problems, submit
commit
4.2 If you think there is a problem, roll back
rollback to save point
(Once submitted using commit, all save points will be cleared and cannot be rolled back)
Acid characteristics of transactions
* Atomicity: Atomicity means that a transaction is an indivisible unit of work, and operations in a transaction either all occur or none occur.
* Consistency: A transaction must change the database from one consistency state to another consistency state
* Durability: The isolation of transactions means that when multiple users access the database concurrently, the database starts a transaction for each user and cannot be interfered by the operation data of other things. 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. Even if the database fails, it should not have any impact.