51可用代码模板网站

咨询热线:

181-1164-6936
  • 1
PHP代码
当前位置:首页>实用代码>PHP代码>

mysqli学习笔记以及事务处理

时间:2017-08-14 14:15点击:

一、数据库连接



    <?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();  
       
     ?>  


51可用模板(代码)网站专为广大站长朋友提供精品模板与代码,同时免费提供网站收录。
本站域名: WWW.51KYDM.COM    联系方式:QQ:182468923    手机:18111646936
本站关键词:网站链接收录网址收录入口网站免费提交收录网站收录提交入口    网站地图
51可用模板
展开