使用mysqli中的预处理和事务处理语句，效率和安全都比mysql有保障
预处理：
prepare($sql); //放到数据库
$stmt->bind_param(“sdis”,$name,$price,$num,$desn); //给占位符传值,类型-变量(不能是值)
$name=”zhangsan”;
$price=56.56;
$num=66;
$desn=”good”;
$stmt->execute(); //执行
echo “最后ID”.$stmt->insert_id;
$stmt->close();
/*=================select语句=================*/
$sql=”select id,name,price,num,desn from shops where id>?”; //准备一条语句放到服务器中
$stmt=$mysqli->prepare($sql); //放到数据库
$stmt->bind_param(“i”,$id); //给占位符传值,类型-变量(不能是值)
$stmt->bind_result($id,$name,$price,$num,$desn); //绑定结果集
$id=99;
$stmt->execute(); //执行
$stmt->store_result(); //一次性讲结果都取过来,才能移动指针和获取总数
//字段信息
$result=$stmt->result_metadata();
while($field=$result->fetch_field()){
echo $field->name;
}
echo “
“;
//记录信息
$stmt->data_seek(2);
while($stmt->fetch()){
echo “$id–$name–$price–$num–$desn
“;
}
echo “记录总数:”.$stmt->num_rows;
$stmt->free_result();
$stmt->close();
?>
事务处理：
set_charset(“utf8″); //设置字符集
$mysqli->autocommit(0); //关闭自动提交
$error=true;
$price=50;
$sql=”Update zhanghaodb set yue=yue-{$price} where name=’zhangsan’”;
$result=$mysqli->query($sql);
if(!$result){
$error=false;
echo “从张三转出失败
”;
}else{
if($mysqli->affected_rows==0){
$error=false;
echo “张三的钱没有变化”;
}else{
echo “张三的钱转出成功
”;
}
}
$sql=”Update zhanghaodb set yue=yue+{$price} where name=’lisi’”;
$result=$mysqli->query($sql);
if(!$result){
$error=false;
echo “从李四转入失败
”;
}else{
if($mysqli->affected_rows==0){
$error=false;
echo “李四的钱没有变化”;
}else{
echo “李四的钱转入成功
”;
}
}
if($error){
echo “转账成功!”;
}else{
echo “转账失败!”;
$mysqli->rollback(); //回滚
}
$mysqli->autocommit(1); //开启自动提交
$mysqli->close();
?>
一次执行多条SQL语句：
50;”;
$sqls.=”delete from shops where id < 20″;
if($mysqli->multi_query($sqls)){
echo “多条语句执行成功!
”;
echo “最后插入的ID:”.$mysqli->insert_id.”
”;
//echo “影响的行数:”.$mysqli->affected_rows; //不准确!
}else{
echo “ERROR”.$mysqli->errno.”—”.$mysqli->error;
}
/*===========有结果集:select===========*/
$sqls=”select current_user();”;
$sqls.=”desc shops;”;
$sqls.=”select * fron shops”;
if($mysqli->multi_query($sqls)){
echo “多条语句执行成功!
”;
do{
$result=$mysqli->store_result(); //获取结果集
echo ‘
| ’.$field->name.’ |
|---|
| ’.$col.’ |
’;
if($mysqli->more_results()){ //判断还有没有结果集
echo “
”;
}
}while($mysqli->next_result()); //取得下一个结果集
}else{
echo “ERROR”.$mysqli->errno.”—”.$mysqli->error;
}
$mysqli->close();
?>
