一、数据库连接
View Code
1 <?php
2 $conn = new mysqli('localhost','root','abc123','newbbs',3306);
3
4 if(mysqli_connect_errno()){
5 echo '数据库连接出错，错误信息：'.mysqli_connect_error();
6 }
7
8 $conn->select_db('test');
9
10 $conn->set_charset('utf8');
11
12
13 $sql = 'select id,username,password from user';
14
15 $result = $conn->query($sql);
16
17 //$result->fetch_array() MYSQLI_NUM MYSQLI_ASSOC MYSQLI_BOTH
18 //$result->fetch_object()
19 //$result->fetch_assoc()
20 //$result->fetch_row()
21 //
22 if($result && $conn->affected_rows){
23 echo '<table width=800 border=1>';
24 while($row = $result->fetch_object()){
25 echo '<tr><td>'.$row->id.'</td><td>'.$row->password.'</td><td>'.$row->username.'</td></tr>';
26 }
27 echo '</table>';
28
29 }else{
30 echo '没有数据';
31 }
32
33 $result->free(); //释放资源
34 //$result->close(); //释放资源
35
36 $conn->close();
37 ?>
二、多语句查询
1 <?php
2 $conn = new mysqli('localhost','root','abc123','newbbs',3306);
3
4 if(mysqli_connect_errno()){
5 echo '数据库连接出错，错误信息：'.mysqli_connect_error();
6 }
7
8 $conn->select_db('test');
9
10 $conn->set_charset('utf8');
11
12 $sql = "select username,password from user where id>60;";
13 $sql.= "select username,password from user where id>50 and id<55;";
14 $sql.= "select username,password from user where id in (56,57,58)";
15
16 $result = $conn->multi_query($sql);
17 var_dump($result);
18
19 //$conn->store_result() 绑定结果集
20 //$conn->next_result() 下一结果集
21 //$conn->more_results() 是否还有结果集
22
23 if($result){
24 do{
25 if($v = $conn->store_result()){
26 echo '<table width=800 border=1>';
27 while($row = $v->fetch_assoc()){
28 echo '<tr><td>'.$row['username'].'</td><td>'.$row['password'].'</td></tr>';
29 }
30 echo '</table>';
31 }
32 if($conn->more_results()){
33 echo '+++++++++++++++++';
34 }
35 }while($conn->next_result());
36 }
37 $conn->close();
38
39 ?>
三、SQL预处理
把SQL语句缓存起来，向里面绑定参数，再发送服务端 //高效 安全
1 <?php
2 $conn = new mysqli('localhost','root','abc123','newbbs',3306);
3 if(mysqli_connect_errno()){
4 echo '数据库连接出错，错误信息：'.mysqli_connect_error();
5 }
6 $conn->select_db('test');
7 $conn->set_charset('utf8');
8
9 //$conn->prepare('sql') 预处理
10 //$stmt->bind_param() 绑定参数 // i int s string d double b binary
11 ////$stmt->bind_result() 绑定结果集
12
13 /*
14 $stmt = $conn->prepare('insert into user (username,password) values (?,?)');
15 $stmt->bind_param('ss',$username,$password);
16 $username = '养生';
17 $password = '升阳';
18 $stmt->execute();
19 */
20 $stmt = $conn->prepare('select username,password from user where id>?');
21 $stmt->bind_param('i',$id);
22 $id=10;
23 $stmt->execute();
24
25 $result = $stmt->bind_result($username,$password);
26
27 $stmt->store_result();
28
29 echo $stmt->num_rows;
30
31 while($stmt->fetch()){ // 遍历
32 echo $username.'-----'.$password.'<br>';
33 }
34
35 $conn->close();
36
37 //在预处理对象当中，如果要直接获得对应的查询出来的行数。需要$stmt->store_result();一次。再来调用$stmt->num_rows;
38 ?>
四、事务
1 <form action=zz.php method=post>
2 帐号：<input type=text name=id>户主：<input type=text name=name><br>
3 转入帐号：<input type=text name=rid>转入人：<input type=text name=rname><br>
4 转入金额：<input type=text name=je><br>
5 <input type=submit value=转账>
6 </form
1 <?php
2
3 $id = $_POST['id'];
4 $name = $_POST['name'];
5
6 $rid = $_POST['rid'];
7 $rname = $_POST['rname'];
8
9 $je = $_POST['je'];
10
11 $conn = new mysqli('localhost','root','abc123','test',3306);
12 if(mysqli_connect_errno()){
13 echo '数据库连接错误:'.mysqli_connect_error();
14 }
15 $conn->set_charset('utf8');
16
17 //innodb 支持事物
18 $conn->autocommit(0); //关闭自动提交 mysql指令： set autocommit=0;
19 $flag = true;
20
21 $sql = "update bank set je=je-{$je} where id={$id} and name='{$name}'";
22 echo $sql;
23 $result = $conn->query($sql);
24
25 if(!$result || !$conn->affected_rows){
26 $flag = false;
27 }
28
29 $sql = "update bank set je=je+{$je} where id={$rid} and name='{$rname}'";
30 echo $sql;
31 $result = $conn->query($sql);
32
33 if($flag && $result && $conn->affected_rows){
34 $conn->commit(); //提交 mysql指令： commit;
35 echo '转账成功';
36 }else{
37 $conn->rollback(); //事物回滚 mysql指令：rollback;
38 echo '转账失败，请联系发卡行';
39 }
40
41 $conn->autocommit(1);
42 $conn->close();
43
44 ?>