本例为预处理操作,预处理操作主要作用为防止SQL注入,时SQL语句与数据脱离
写操作(以新增为例):$data = ['name'=>'陈真','email'=>'809332774@qq.com','password'=>'223456'];
//用?代替数据
$sql = "INSERT user1 SET name=?,email=?,password=sha1(?)";
//准备sql语句生成 mysql_stmt对象
$mysql_stmt = $mysql->prepare($sql);
//绑定参数 bind_param(type,data);
$mysql_stmt->bind_param("sss",$data['name'],$data['email'],$data['password']);
//执行
if($mysql_stmt->execute()){
echo "成功插入".$mysql_stmt->affected_rows."条数据,新增主键ID为".$mysql_stmt->insert_id;
}else{
echo "插入失败,错误为: $mysql_stmt->error";
}
$mysql->close();
总结:1.$mysql_stmt = $mysql->prepare($sql) //生成$mysql_stmt对象
2.$mysql_stmt->bind_param(type,data) //type对照data排列先后 列出数据类型 i整形 s字符
3.$mysql_stmt->execute();
success: $mysql_stmt->afftect_rows .insert_id
error : $mysql_stmt->error
print_f($mysql_stmt);生成如下对象:mysqli_stmt Object
(
[affected_rows] => 0
[insert_id] => 0
[num_rows] => 0
[param_count] => 3
[field_count] => 0
[errno] => 0
[error] =>
[error_list] => Array
(
)
[sqlstate] => 00000
[id] => 1
)
读操作(查询):$sql = "SELECT `id`,`name`,`email` FROM user1 WHERE id=? AND name=?";
$data = [1,"wangkai"];
//绑定
$mysql_stmt = $mysql->prepare($sql);
$mysql_stmt->bind_param('is',$data[0],$data[1]);
if($mysql_stmt->execute()){
//存储结果集
$mysql_stmt->store_result();
//判断结果集数量
if($mysql_stmt->num_rows>0){
echo '查询到'.$mysql_stmt->num_rows.'条数据';
//绑定结果集对应的字段
$mysql_stmt->bind_result($id,$name,$email);
//fetch()取出一条数据,fetchA
while($mysql_stmt->fetch()){
echo $id.$name.$email;
};
$mysql_stmt->free_result();
$mysql_stmt->close();
}else{
echo '没有查询到数据';
}
}else{
echo '查询失败请检查'.$mysql_stmt->error;
}
总结:1.$mysql_stmt = $mysql->prepare
2.$mysql_stmt->bind_param();
3.$mysql_stmt->store_result();//存储结果集
3.1 $mysql_stmt->num_rows();//结果集数量
5.$mysql_stmt->bind_result(变量);//为结果集绑定变量
6.$mysql_stmt->fetch() //输出变量,每次输出单条
准备->绑定参数->存储结果集->结果集绑定变量