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

?>