MySQLi的高级应用

事务处理

基本格式为：

$mysqli->autocommit(FALSE);    //关闭自动提交    
/*
执行SQL语句
*/
if(SQL语句执行成功){
    $mysqli->commit();
    $mysqli->autocommit(TRUE);
}else{
    $mysqli->rollback();
}

例子：

$mysqli->autocommit(FALSE);

$query1 = "UPDATE bank SET money=money-1500 WHERE account='201101'";
$res1 = $mysqli->query($query1);
$res1_affect = $mysqli->affected_rows;

$query2 = "UPDATE bank SET money=money+1500 WHERE account='201112'";
$res2 = $mysqli->query($query2);
$res2_affect = $mysqli->affected_rows;

if($res1_affect>0 && $res2_affect>0){
    $mysqli->commit();
    echo "转账成功";
    $mysqli->autocommit(TRUE);
}else{
    $mysqli->rollback();
    echo "转账失败";
}

预处理

预处理操作主要分为三步：

1. 准备预处理语句

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

1. 绑定参数

$mysqli_stmt->bind_param();

1. 执行预处理语句

$mysqli_stmt->execute();

预处理执行插入操作

$query = "INSERT bank(account,money) VALUES(?,?)";

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

$account = "201122";
$money = 8888;
$mysqli_stmt->bind_param('si',$account,$money);//string:account,int:money

if($mysqli_stmt->execute()){
    echo "插入编号：".$mysqli_stmt->insert_id;
    echo "<br/>影响行数：".$mysqli_stmt->affected_rows;
}else{
    echo "插入错误：".$mysqli_stmt->error;
}

预处理防止SQL注入

预处理操作可以用在登陆操作，防止SQL注入。预处理操作，不同于一般的拼接字符串的查询操作，而是使用的传参的方式。

$mysqli_stmt->store_result();  //将查询到的结果进行保存

$mysqli_stmt->num_rows;    //查询到的结果的行数

$query = "SELECT * FROM user WHERE username=? AND password=?";

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

$username = $_GET['username'];  //使用GET方式是为了测试方便
$password = $_GET['password'];
$mysqli_stmt->bind_param('ss',$username,$password);//string:username,string:password

if($mysqli_stmt->execute()){
   $mysqli_stmt->store_result();
   if($mysqli_stmt->num_rows>0){
       echo "登陆成功";
   }else{
       echo "登陆失败";
   }
}else{
   echo "查询失败";
}

预处理执行查询操作

$mysqli_stmt->bind_result();   //绑定查询到的结果到变量

$mysqli_stmt->fetch(); //通过变量，获取查询到的结果

$query = "SELECT id,account,money FROM bank WHERE id>=?";

$mysqli_stmt = $mysqli->prepare($query);
$id = 1;
$mysqli_stmt->bind_param('i',$id);  //int:$id

if($mysqli_stmt->execute()){
    $mysqli_stmt->bind_result($id,$account,$money);
    while ($mysqli_stmt->fetch()) {
        echo "编号：".$id."<br/>";
        echo "账户：".$account."<br/>";
        echo "余额：".$money;
        echo "<hr/>";
    }
}