MySQLi的高级应用

来源：互联网发布：最优化理论推荐教材编辑：程序博客网时间：2024/05/19 09:11

事务处理

基本格式为：

$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 "转账失败";}

预处理

这里写图片描述

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

准备预处理语句

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

绑定参数

$mysqli_stmt->bind_param();

执行预处理语句

$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:moneyif($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:passwordif($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:$idif($mysqli_stmt->execute()){    $mysqli_stmt->bind_result($id,$account,$money);    while ($mysqli_stmt->fetch()) {        echo "编号：".$id."<br/>";        echo "账户：".$account."<br/>";        echo "余额：".$money;        echo "<hr/>";    }}

0 0