PHP mysqli extension library (object-oriented/database operation encapsulation/transaction control/precompilation), mysqli object-oriented

1. The difference with mysql extension library:

(1) Higher security and stability

(2 provides two styles of object-oriented and process-oriented

2. Extension=php_mysqli.dll in php.ini is unsealed

3. Object-oriented: query list

 1 <?php
 2 
 3   //mysqli 操作數(shù)據(jù)（面向?qū)ο箫L(fēng)格）
 4   
 5   #1、創(chuàng)建Mysql對象
 6   
 7   $mysqli=new MySQLi("127.0.0.1","root","daomul","test");
 8   if(!$mysqli)
 9   {
10        die("連接失敗！".$mysqli->connect_error);
11   }
12   
13   #2、操作數(shù)據(jù)庫
14   
15   $sql="select * from user1";
16   $res=$mysqli->query($sql);
17   #3、處理結(jié)果
18   
19   while($row=$res->fetch_row())
20   {
21       foreach($row as $key=> $val)
22       {
23           echo "-- $val";
24       }
25       echo "<br/>";
26   }
27   #4、關(guān)閉資源
28   $res->free();//釋放內(nèi)存
29   $mysqli->close();//關(guān)閉連接
30   
31 ?>

4. Object-oriented: implement after encapsulating the class

4.1 Sqliconnect.class.php

 1 <?php
 2 
 3    Class Sqliconnect
 4    {
 5         private $mysqli;
 6         private static $host="127.0.0.1";
 7         private static $root="root";
 8         private static $password="daomul";
 9         private static $db="test";
10         
11         function __construct()
12         {
13              $this->mysqli=new MySQLi(self::$host,self::$root,self::$password,self::$db);
14              if(!$this->mysqli)
15              {
16                    die("數(shù)據(jù)庫連接失敗！".$this->mysqli->connect_error);
17              }
18              
19              $this->mysqli->query("set names utf8");
20         }
21         
22         //查詢操作
23         public function excute_dql($sql)
24         {
25               $res=$this->mysqli->query($sql) or die("數(shù)據(jù)查詢失敗".$this->mysqli->error);
26               return $res;
27               
28         }
29         
30         //增刪改操作
31         public function excute_dml($sql)
32         {
33               $res=$this->mysqli->query($sql) or die("數(shù)據(jù)操作失敗".$this->mysqli->error);
34               if(!$res)
35               {
36                    echo "數(shù)據(jù)操作失敗";
37               }
38               else
39               {
40                    if($this->mysqli->affected_rows>0)
41                    {
42                          echo "操作成功！";
43                    }
44                    else
45                    {
46                         echo "0行數(shù)據(jù)受影響！";
47                    }
48               }
49         }
50         
51    }
52 ?>

4.2 Call page startsqli.php

 1 <?php
 2 
 3   //mysqli 操作數(shù)據(jù)（面向?qū)ο箫L(fēng)格）
 4   
 5   
 6   require_once "Sqliconnect.class.php";
 7   
 8   $Sqliconnect=new Sqliconnect();
 9   
10   //$sql="insert into user1(name,password,email,age) values('帝都',md5('gg'),'sd@sohu.com',23)";
11   //$sql="delete from user1 where id=11";
12   //$res=$Sqliconnect->excute_dml($sql);
13   
14   $sql="select name from user1;";
15   $res=$Sqliconnect->excute_dql($sql);
16   while($row=$)
17   
18   $res->free();
19 ?>

5. Execute multiple database statements at the same time multiQuery.php

 1 <?php
 2 
 3   //mysqli 操作數(shù)據(jù)（面向?qū)ο箫L(fēng)格）
 4   
 5   #1、創(chuàng)建Mysql對象
 6   
 7   $mysqli=new MySQLi("127.0.0.1","root","daomul","test");
 8   if(!$mysqli)
 9   {
10        die("連接失??！".$mysqli->connect_error);
11   }
12   
13   #2、操作數(shù)據(jù)庫
14   
15   $sqls="select * from user1;";
16   $sqls.="select * from user1";
17   
18   #3、處理結(jié)果
19   
20   if($res=$mysqli->multi_query($sqls))
21   {
22        echo "211";
23      do 
24      {
25           //從mysqli連續(xù)取出第一個結(jié)果集
26           $result=$mysqli->store_result();
27           
28           //顯示mysqli result對象
29           while($row=$result->fetch_row())
30           {
31             foreach($row as $key=> $val)
32             {
33                 echo "-- $val";
34             }
35            echo "<br/>";
36          }
37          
38        $result->free();//及時釋放當(dāng)前結(jié)果集，并進入下一結(jié)果集
39          
40          //判斷是否有下一個結(jié)果集
41          if(!$mysqli->more_results())
42          {
43            break;
44          }
45        echo "<br/>************新的結(jié)果集**************";
46        
47      }while($mysqli->next_result());
48  }
49  
50   #4、關(guān)閉資源
51   $mysqli->close();//關(guān)閉連接  
52   
53   
54 ?>

6. Transaction control

 1 <?php
 2 
 3   //mysqli 操作數(shù)據(jù)（面向?qū)ο箫L(fēng)格）
 4   
 5  
 6    // 數(shù)據(jù)庫 ：create table account(id int primary key,balance float);
 7    
 8   $mysqli=new MySQLi("127.0.0.1","root","daomul","test");
 9   if(!$mysqli)
10   {
11        die("數(shù)據(jù)庫連接失??！".$mysqli->connect_error);
12   }
13   //將提交設(shè)為false
14   $mysqli->autocommit(false);
15   
16   $sql1="update account set balance=balance+1 where id=1;";//沒錯的語句
17   $sql2="update accounterror2 set balance=balance-1 where id=2";//有錯的語句
18   
19   $res1=$mysqli->query($sql1);
20   $res2=$mysqli->query($sql2);
21   
22   if(!$res1||!$res2)
23   {
24       //回滾：其中一個不成功即回滾不提交
25        echo "有錯，回滾，請重新提交！";
26        $mysqli->rollback();//die("操作失??！".$mysqli->error);
27   }
28   else
29   {
30       //所有均成功則提交
31        echo "所有提交成功！";
32        $mysqli->commit();
33   }
34   
35   $mysqli->close();
36   /* 
37     1、 start transaction; 開啟事務(wù)
38     2、svaepoint a;    做保存點
39     3、執(zhí)行操作1; 
40     4、 svaepoint b;
41     5、執(zhí)行操作2;
42     ...
43     6、rollback to a/b; 回滾或者是提交
44     7、commit 
45     
46     事務(wù)控制特點acid  原子性/一致性/隔離性/持久性
47    */
48 ?>

7. Preprocessing technology

It mainly streamlines the connection and compilation process, and can also prevent SQL injection

7.1 Pre-compile and insert multiple data

 1 <?php
 2 
 3   //mysqli 預(yù)編譯演示
 4   
 5   #1、創(chuàng)建mysqli對象
 6   $mysqli=new MySQLi("127.0.0.1","root","daomul","test");
 7   if(!$mysqli)
 8   {
 9        die("數(shù)據(jù)庫連接失??！".$mysqli->connect_error);
10   }
11   
12   #2、創(chuàng)建預(yù)編譯對象
13   $sql="insert into user1(name,password,email,age) values(?,?,?,?);";//暫時不賦值，用問號代替
14   $stmt=$mysqli->prepare($sql) or die($mysqli->error);
15  
16   /********************************可重復(fù)執(zhí)行時需要的代碼start*********************************/
17   #3、綁定參數(shù)
18   $name='小明5';
19   $password='34f';
20   $email='ssd@qq.com';
21   $age='1';
22   
23   #4、參數(shù)賦值（第一個參數(shù)指代參數(shù)的類型縮寫，string-s,int-i,double-d,bool-b
24   $stmt->bind_param("sssi",$name,$password,$email,$age);
25   
26   #5、執(zhí)行代碼（返回布爾類型）
27   $flag=$stmt->execute();
28   
29  /********************************可重復(fù)執(zhí)行時需要的代碼 end************************************/
30   
31   #6、結(jié)果以及釋放
32   
33   if(!$flag)
34   {
35       die("操作失敗".$stmt->error);
36   }
37   else
38   {
39       echo "操作成功！";
40   }
41   
42   $mysqli->close();
43   
44  
45 ?>

7.2 Pre-compiled query for multiple data

 1 <?php
 2 
 3   //mysqli 預(yù)編譯演示
 4   
 5   #1、創(chuàng)建mysqli對象
 6   $mysqli=new MySQLi("127.0.0.1","root","daomul","test");
 7   if(!$mysqli)
 8   {
 9        die("數(shù)據(jù)庫連接失?。?quot;.$mysqli->connect_error);
10   }
11   
12    /********************************可重復(fù)執(zhí)行時需要的代碼 start*******************************/
13  
14   #2、創(chuàng)建預(yù)編譯對象
15   $sql="select id,name,email from user1 where id>?;";//id,name,email和后面的結(jié)果集bind_result()對應(yīng)
16   $stmt=$mysqli->prepare($sql) or die($mysqli->error);
17  
18   #3、綁定參數(shù)
19   $id=5;
20   
21   #4、參數(shù)賦值（第一個參數(shù)指代參數(shù)的類型縮寫，string-s,int-i,double-d,bool-b
22   $stmt->bind_param("i",$id);//綁定參數(shù)
23   $stmt->bind_result($id,$name,$email);//綁定結(jié)果集
24   
25   #5、執(zhí)行代碼（返回布爾類型）
26   $stmt->execute();
27   
28   #6、取出結(jié)果集顯示
29   while($stmt->fetch())
30   {
31       echo "<br/>$id--$name--$email";
32   }
33   
34   /********************************可重復(fù)執(zhí)行時需要的代碼 end*******************************/
35   
36   #7、結(jié)果以及釋放
37   
38   //釋放結(jié)果
39   $stmt->free_result();
40   //關(guān)閉預(yù)編譯語句
41   $stmt->close();
42   //關(guān)閉數(shù)據(jù)庫連接
43   $mysqli->close();
44   
45  
46 ?>

8. Other functions

(1 Get the number of rows and columns num_rows field_count

(2 Get a column of the result set: header, for example

$result=$mysqli->query();

$result->fetch_field();

(3 Get data

$row=$result->fetch_row(); //Get each row of data

Taking each data through Foreach ($ Row as $ Val) {}