使用MySQL stmt 预处理对象进行增、删、改、查
操作步骤:
连接数据库
拼接SQL语句
初始化 stmt 对象
如果有参数进行参数绑定
执行SQL语句
得到返回的结果集或者受影响的行,存在结果集则遍历
注销 stmt 对象
关闭 数据库连接
具体操作如下:
Insert:
<?php /** * Created by PhpStorm. * User: Administrator * Date: 2018/4/25 * Time: 11:12 */ //1.连接数据库 require 'conn.php'; //创建 sql $sql = "INSERT IGNORE staff SET name=?,sex=?,age=?,salary=?"; $name='成昆'; $sex=0; $age=55; $salary=6000; //创建 stmt 初始化对象 $stmt = mysqli_stmt_init($conn); //检测sql语句是否正确, 成功返回 true,错误返回 false if (mysqli_stmt_prepare($stmt,$sql)){ //绑定 sql 语句中的占位符 , mysqli_stmt_bind_param($stmt,'siii',$name,$sex,$age,$salary); //执行SQL语句,判断是否执行成功 mysqli_stmt_execute($stmt); //判断是否存在受影响的记录数,大于 0 存在则执行成功 if(mysqli_stmt_affected_rows($stmt)>0){ echo '新增成功,ID是:'.mysqli_stmt_insert_id($stmt); //输出新增的ID编号 }else{ echo '没有新增内容'; } }else { //返回SQL检测阶段的错误 exit(mysqli_stmt_errno($stmt).':'.mysqli_stmt_error($stmt)); } //注销 stmt 对象 mysqli_stmt_close($stmt); //关闭数据库连接 mysqli_close($conn);
Query:
<?php /** * Created by PhpStorm. * User: Administrator * Date: 2018/4/25 * Time: 1:17 */ require 'conn.php'; //定义 sql $sql = "SELECT name,sex,age,salary FROM staff"; //创建 stmt 对象传入数据库连接 $stmt = mysqli_stmt_init($conn); //检测sql语句是否执行成功 if(mysqli_stmt_prepare($stmt,$sql)){ //执行查询 mysqli_stmt_execute($stmt); //获取结果集 mysqli_stmt_store_result($stmt); //绑定结果集中的数据 mysqli_stmt_bind_result($stmt,$name,$sex,$age,$salary); //检测结果集是否为空 if(mysqli_stmt_num_rows($stmt)>0){ while (mysqli_stmt_fetch($stmt)){ echo "[{$name}]-->[{$sex}]-->[{$age}]-->[{$salary}]<br>"; } }else{ echo json_encode(['error'=>0]); } }else{ //输出错误信息 exit(mysqli_stmt_errno($stmt).':'.mysqli_stmt_error($stmt)); } //注销 stmt 对象 mysqli_stmt_close($stmt); //关闭数据库连接 mysqli_close($conn); ?>
Delete:
<?php /** * Created by PhpStorm. * User: Administrator * Date: 2018/4/25 * Time: 11:12 */ //连接数据库 require 'conn.php'; //创建 sql 语句 $sql="DELETE FROM staff WHERE name=?"; $name='成昆'; //初始化 stmt 对象 $stmt = mysqli_stmt_init($conn); //检测 sql 语句 if(mysqli_stmt_prepare($stmt,$sql)){ //如果没有错误,开始绑定参数 mysqli_stmt_bind_param($stmt,'s',$name); //开始执行 sql mysqli_stmt_execute($stmt); //判断是否执行成功,如果受影响的记录大于 0 则成功 if(mysqli_stmt_affected_rows($stmt)>0){ echo '成功更新了:'.mysqli_stmt_affected_rows($stmt).'条记录'; }else{ echo '没有找到更新的条件'; } }else { exit(mysqli_stmt_errno($stmt).':'.mysqli_stmt_error($stmt)); } //注销 stmt 对象 mysqli_stmt_close($stmt); //判断数据库连接 mysqli_close($conn);
Update:
<?php /** * Created by PhpStorm. * User: Administrator * Date: 2018/4/25 * Time: 11:12 */ //连接数据库 require 'conn.php'; //创建 sql 语句 $sql="UPDATE staff SET salary=5500 WHERE name=?"; $name='成昆'; //初始化 stmt 对象 $stmt = mysqli_stmt_init($conn); //检测 sql 语句 if(mysqli_stmt_prepare($stmt,$sql)){ //如果没有错误,开始绑定参数 mysqli_stmt_bind_param($stmt,'s',$name); //开始执行 sql mysqli_stmt_execute($stmt); //判断是否执行成功,如果受影响的记录大于 0 则成功 if(mysqli_stmt_affected_rows($stmt)>0){ echo '成功更新了:'.mysqli_stmt_affected_rows($stmt).'条记录'; }else{ echo '没有找到更新的条件'; } }else { exit(mysqli_stmt_errno($stmt).':'.mysqli_stmt_error($stmt)); } //注销 stmt 对象 mysqli_stmt_close($stmt); //判断数据库连接 mysqli_close($conn);