一、数据库连接
二、多语句查询
<?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();
?>