在php中使用mysqli中的预处理和事务处理语句操作MySql

使用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 ‘

’;
echo ‘
’;
while($field=$result->fetch_field()){
echo ‘

’;
}
echo ‘

’;

while($row=$result->fetch_assoc()){
echo ‘

’;
foreach($row as $col){
echo ‘

’;
}
echo ‘

’;
}
echo ‘

’.$field->name.’
’.$col.’

’;

if($mysqli->more_results()){ //判断还有没有结果集
echo “

”;
}

}while($mysqli->next_result()); //取得下一个结果集
}else{
echo “ERROR”.$mysqli->errno.”—”.$mysqli->error;
}

$mysqli->close();
?>