mysql 预处理创建事务_在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 ‘

’.$field->name.’

’;

}

echo ‘

’;

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

echo ‘

’;

foreach($row as $col){

echo ‘

’.$col.’

’;

}

echo ‘

’;

}

echo ‘

’;

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

echo “

”;

}

}while($mysqli->next_result()); //取得下一个结果集

}else{

echo “ERROR”.$mysqli->errno.”—”.$mysqli->error;

}

$mysqli->close();

?>