mysqli学习笔记 mysqli连接，multi_query多语句查询，SQL预处理stmt，事务处理 .

一、数据库连接

<?php  
 $conn = new mysqli('localhost','root','abc123','newbbs',3306);  
   
 if(mysqli_connect_errno()){  
     echo '数据库连接出错，错误信息：'.mysqli_connect_error();  
 }  
 $conn->select_db('test');  
   
 $conn->set_charset('utf8');  
   
   
 $sql = 'select id,username,password from user';  
   
 $result = $conn->query($sql);  
   
 //$result->fetch_array()   MYSQLI_NUM MYSQLI_ASSOC MYSQLI_BOTH   
 //$result->fetch_object()   
 //$result->fetch_assoc()   
 //$result->fetch_row()   
 //   
 if($result && $conn->affected_rows){  
     echo '<table width=800 border=1>';  
     while($row = $result->fetch_object()){    
         echo '<tr><td>'.$row->id.'</td><td>'.$row->password.'</td><td>'.$row->username.'</td></tr>';  
     }  
     echo '</table>';  
   
 }else{  
     echo '没有数据';  
 }  
   
 $result->free();  //释放资源   
 //$result->close();  //释放资源   
   
 $conn->close();  
 ?>  

二、多语句查询

 <?php  
 $conn = new mysqli('localhost','root','abc123','newbbs',3306);  
   
 if(mysqli_connect_errno()){  
     echo '数据库连接出错，错误信息：'.mysqli_connect_error();  
 }  
   
 $conn->select_db('test');  
   
$conn->set_charset('utf8');  
  
$sql = "select username,password from user where id>60;";  
$sql.= "select username,password from user where id>50 and id<55;";  
$sql.= "select username,password from user where id in (56,57,58)";  
  
$result = $conn->multi_query($sql);  
var_dump($result);  
  
//$conn->store_result() 绑定结果集   
//$conn->next_result()  下一结果集   
//$conn->more_results() 是否还有结果集   
  
if($result){  
    do{  
        if($v = $conn->store_result()){  
            echo '<table width=800 border=1>';  
            while($row = $v->fetch_assoc()){                  
                echo '<tr><td>'.$row['username'].'</td><td>'.$row['password'].'</td></tr>';  
            }  
            echo '</table>';          
        }          
        if($conn->more_results()){  
            echo '+++++++++++++++++';  
        }      
    }while($conn->next_result());  
}  
$conn->close();  
  
?>

三、SQL预处理
把SQL语句缓存起来，向里面绑定参数，再发送服务端 //高效 安全

<?php  
 $conn = new mysqli('localhost','root','abc123','newbbs',3306);  
 if(mysqli_connect_errno()){  
     echo '数据库连接出错，错误信息：'.mysqli_connect_error();  
  }  
  $conn->select_db('test');  
  $conn->set_charset('utf8');  
    
  //$conn->prepare('sql') 预处理   
 //$stmt->bind_param()  绑定参数  // i int  s  string  d double  b binary   
 $stmt->bind_result()  绑定结果集   
   
 /* 
 $stmt = $conn->prepare('insert into user (username,password) values (?,?)'); 
 $stmt->bind_param('ss',$username,$password); 
 $username = '养生'; 
 $password = '升阳'; 
 $stmt->execute(); 
  */  
 $stmt = $conn->prepare('select username,password from user where id>?');  
 $stmt->bind_param('i',$id);  
 $id=10;  
 $stmt->execute();  
   
 $result = $stmt->bind_result($username,$password);  
   
 $stmt->store_result();  
   
 echo $stmt->num_rows;  
   
 while($stmt->fetch()){   // 遍历   
     echo $username.'-----'.$password.'<br>';  
 }  
   
 $conn->close();  
   
 //在预处理对象当中，如果要直接获得对应的查询出来的行数。需要$stmt->store_result();一次。再来调用$stmt->num_rows;   
 ?>  

四、事务

<form action=zz.php method=post>  
    帐号：<input type=text name=id>户主：<input type=text name=name><br>  
    转入帐号：<input type=text name=rid>转入人：<input type=text name=rname><br>  
    转入金额：<input type=text name=je><br>  
    <input type=submit value=转账>  
</form>  

<?php  
    
  $id = $_POST['id'];  
  $name = $_POST['name'];  
    
  $rid = $_POST['rid'];  
  $rname = $_POST['rname'];  
    
  $je = $_POST['je'];  
   
 $conn = new mysqli('localhost','root','abc123','test',3306);  
 if(mysqli_connect_errno()){  
     echo '数据库连接错误:'.mysqli_connect_error();  
 }  
 $conn->set_charset('utf8');  
   
 //innodb 支持事物   
 $conn->autocommit(0);  //关闭自动提交  mysql指令： set autocommit=0;   
 $flag = true;  
   
 $sql = "update bank set je=je-{$je} where id={$id} and name='{$name}'";  
 echo $sql;  
 $result = $conn->query($sql);  
   
 if(!$result || !$conn->affected_rows){  
     $flag = false;  
 }  
   
 $sql = "update bank set je=je+{$je} where id={$rid} and name='{$rname}'";  
 echo $sql;  
 $result = $conn->query($sql);  
   
 if($flag && $result && $conn->affected_rows){  
     $conn->commit();   //提交  mysql指令： commit;   
     echo '转账成功';  
 }else{  
     $conn->rollback();    //事物回滚 mysql指令：rollback;   
     echo '转账失败，请联系发卡行';  
 }  
   
 $conn->autocommit(1);  
 $conn->close();  
   
 ?>