【二十二】mysqli事务处理与预处理总结

php
//数据库：create table account(id int primary key,balance float);
//银行转账
    $mysqli=new MySQLi("localhost",'root','','test');
    if ($mysqli->connect_error) {
        echo $mysqli->connect_error;
    }
    //将提交设为假
    $mysqli->autocommit(FALSE);
    $sql1="update account22 set balance=balance+2 where id='1';";
    $sql2="update account1 set balance=balance-2 where id='2';";
    $a=$mysqli->query($sql1);
    $b=$mysqli->query($sql2);
    //判断语句是否为真
    if (!$a || !$b) {
        //其中有一个不成功即回滚不提交
        echo "失败回滚".$mysqli->error;
        $mysqli->rollback();
    }else{
        //所有均成功则提交
        echo "成功";
        $mysqli->commit();
    }
    $mysqli->close();
?>

 1 php
 2     $mysqli=new mysqli("127.0.0.1","root","","test");
 3     if (!$mysqli->connect_error) {
 4         echo $mysqli->connect_error;
 5     }
 6 //预处理及绑定
 7     $sql="INSERT INTO user1 (name,Password,Email,age)VALUES ( ?,?,?,?);";
 8     //prepare 准备执行的sql语句
 9     $sql_stmt=$mysqli->prepare($sql) or die($mysqli->error);
10     //参数绑定
11     //bind_param：将变量作为参数绑定到预处理语句
12     //这里的类型和顺序要对应
13     $sql_stmt->bind_param('sssi',$name,$Password,$Email,$age);
14 // 设置参数并执行
15     $name='sunwukong';
16     $Password='123';
17     $Email='sunwukong@qq.com';
18     $age='200';
19     //执行准备好的sql
20     $b=$sql_stmt->execute();
21 //添加第二个
22     $name='zhubajie';
23     $Password='123';
24     $Email='zhubajie@qq.com';
25     $age='200';
26     $b=$sql_stmt->execute();
27 //判断是否插入成功
28     if (!$b) {
29         echo "操作失败".$sql_stmt->error;
30     }else{
31         echo "操作成功";
32     }
33     //释放
34     $mysqli->close();
35 ?>

 1  php
 2     $mysqli=new mysqli("127.0.0.1","root","","test");
 3     if (!$mysqli->connect_error) {
 4         echo $mysqli->connect_error;
 5     }
 6     //1.预处理及绑定
 7     //2.参数设置
 8     //3.执行
 9     $sql="select name from user1 where id>?";
10     $stmt=$mysqli->prepare($sql) or die($mysqli->error) ;
11     $id=3;
12     $stmt->bind_param("i",$id);
13     //bind_result：将变量绑定到预先准备好的语句以获得结果存储
14     $stmt->bind_result($name);
15     $stmt->execute();
16     while ($stmt->fetch()) {
17         echo $name."
";
18     }

　　　　$stmt->free_result();
　　　　$stmt->close();

19 ?>