PHP mysqli Extension Library (Object-oriented/database operations encapsulation/transaction control/precompilation)

1. Differences from MySQL extension libraries:

(1 higher security, more stability

(2 provides both object-oriented and process-oriented styles

2, php.ini in the Extension=php_mysqli.dll lifting the seal

3. Object-oriented: Query list

1 <?php 2  3   //mysqli Operational Data (object-oriented style) 4    5   #1, creating MySQL object 6    7   $mysqli =new mysqli ("127.0.0.1", " Root "," Daomul "," Test "); 8   if (! $mysqli) 9   {ten die        ("Connection failed! ". $mysqli->connect_error);   }12   #2, operating database   $sql =" SELECT * from User1 ";   res= $mysqli->query ($sql);   #3, Results of processing (   $row = $res->fetch_row ())       foreach ($row as $key + $val) @           echo "-$val";       }25       echo "<br/>";   }27   #4, close resources   $res->free ();//Release Memory   $mysqli->close ();//close connection   ?>

4, Object-oriented: encapsulation class after implementation

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"; function __construct ()->mysq                    Li=new mysqli (self:: $host, Self:: $root, Self:: $password, Self:: $db), if (! $this->mysqli) 15 {16 Die ("Database connection failed! ". $this->mysqli->connect_error);}18 $this->mysqli->query (" Set Nam               Es UTF8 "); 20}21 22//query operation: Public function EXCUTE_DQL ($sql) 24 {25               $res = $this->mysqli->query ($sql) or Die ("Data query Failed". $this->mysqli->error); return $res; 27 28}29 30//delete and change operation to public function Excute_dml ($sql) ($re s= $this->mysqli->query ($sql) oR die ("Data operation failed". $this->mysqli->error); if (! $res) + {echo "Data operation failed" ; PNs}38 Else39 {$this->mysqli->affected_rows>0 The operation was successful! "}44 else45 {echo" 0 rows of data are affected! ";}48}49}50 Wuyi}52?>

4.2 Calling page startsqli.php

1 <?php 2  3   //mysqli Operational data (object oriented style) 4    5    6   require_once "Sqliconnect.class.php"; 7    8   $ Sqliconnect=new Sqliconnect (); 9   //$sql = "INSERT INTO User1 (name,password,email,age) VALUES (' Royal Park ', MD5 (' GG '), ' [email protected] ', 23)"; 11   //$sql = "Delete from User1 where id=11";   //$res = $Sqliconnect->excute_dml ($sql);   $sql = "SELECT name from User1;";   $res = $Sqliconnect->excute_dql ($sql), while   ($row =$)   $res->free ();?>

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

 1 <?php 2 3//mysqli Operational data (object-oriented style) 4 5 #1, creating MySQL object 6 7 $mysqli =new mysqli ("127.0.0.1", "root", "Daomul", "Te St "); 8 if (! $mysqli) 9 {ten die ("Connection failed! ". $mysqli->connect_error);}12 #2, Operation database, $sqls =" select * from User1; "; $sqls. = "SELECT * from User1"; #3, results of treatment if ($res = $mysqli->multi_query ($sqls)) {echo]           211 "; 24 = 25//The first result set is fetched sequentially from mysqli $result = $mysqli->store_result (); 27 28 Display Mysqli Result object ($row = $result->fetch_row ()) ($row as $          Key=> $val) ($val ","}35 echo "," <br/> "; 36 }37 $result->free ();//Release the current result set in time and go to the next result set 39 40//Determine if there is a next result set, if (! $mys Qli->more_results ()) (Break;44}45 echo "<br/>************ new result set ********* *****";}while ($mysqli->next_result ()),}49 #4, close resources $mysqli->close ();//close connection 52 53 54? Gt

6. Transaction control

 1 <?php 2 3//mysqli Operational data (object oriented style) 4 5 6//Database: CREATE TABLE account (ID int primary key,balance float); 7 8 $mysqli =new mysqli ("127.0.0.1", "root", "Daomul", "Test"); 9 if (! $mysqli) ten die ("Database connection failed!  ". $mysqli->connect_error); 12}13//Set the submission to False14 $mysqli->autocommit (false); $sql 1=" Update account set Balance=balance+1 where id=1; /Yes Statement $sql 2= "update Accounterror2 set balance=balance-1 where id=2";//Error statement $res 1= $mysqli->query ($sql 1); 2 0 $res 2= $mysqli->query ($sql 2), if (! $res 1| |! $res 2) 23 {24//rollback: One unsuccessful rollback does not submit echo "error, rollback, please resubmit!" "; $mysqli->rollback ();//die (" Operation failed! ". $mysqli->error);}28 else29 {30//All success is submitted to echo" All submitted successfully! $mysqli->commit (),}34 $mysqli->close (), 36/* 37 1, start transaction;    Open Transaction 38 2, Svaepoint A; Do save Point 39 3, perform operation 1; 40 4, Svaepoint b;41 5, perform operation 2;42 ... 43 6, rollback to A/b; Rollback or commit 44    7. Commit 45 46 transaction Control Characteristics acid atomicity/consistency/isolation/durability */48?> 

7. Pretreatment Technology

Mainly in the connection and compilation process is streamlined, but also SQL to prevent injection

7.1 Precompilation Inserts multiple data

 1 <?php 2 3//mysqli Pre-compilation Demo 4 5 #1, create mysqli object 6 $mysqli =new mysqli ("127.0.0.1", "root", "Daomul", "Test"); 7 if (! $mysqli) 8 {9 Die ("Database connection failed! ". $mysqli->connect_error);}11 #2, creating precompiled objects $sql =" INSERT INTO User1 (name,password,email,age) VALUES (?,?,?, ?);";/ /temporarily unassigned, use a question mark instead of $stmt = $mysqli->prepare ($sql) or Die ($mysqli->error); 15 16/******************************** The code required for repeatable execution is START*********************************/17 #3, binding parameters $name = ' xiaoming 5 '; $password = ' 34f '; $email = ' [email&nb Sp;protected] '; $age = ' 1 '; #4, Parameter assignment (the first parameter refers to the type abbreviation of the parameter, string-s,int-i,double-d,bool-b24 $stmt->bind_param ("SSS I ", $name, $password, $email, $age); #5, execution code (return boolean type) $flag = $stmt->execute (); 28 29/************************ The code required for repeatable execution End************************************/30 #6, results, and release of the (! $flag) ". $stmt->error),}37 else38 {echo" Operation succeeded! ";}41 $mysqli->close (); 43 44?> 

7.2 precompiling queries for multiple data

 1 <?php 2 3//mysqli Pre-compilation Demo 4 5 #1, create mysqli object 6 $mysqli =new mysqli ("127.0.0.1", "root", "Daomul", "Test"); 7 if (! $mysqli) 8 {9 Die ("Database connection failed! ". $mysqli->connect_error); 10}11 12/******************************** code required for repeatable execution start************************* /13 #2, create precompiled objects $sql = "Select Id,name,email from User1 where id>?;"; /id,name,email and subsequent result sets Bind_result () correspond to $stmt = $mysqli->prepare ($sql) or Die ($mysqli->error), and the binding parameter is #3 D=5;20 #4, Parameter assignment (the first parameter refers to the type abbreviation of the parameter, string-s,int-i,double-d,bool-b22 $stmt->bind_param ("i", $id);//binding parameter is $stmt-&GT ; Bind_result ($id, $name, $email);//bind result set #5, execute code (return Boolean type), $stmt->execute (), #6, remove the result set to show the while ($st Mt->fetch ()) {echo "<br/> $id-$name-$email"; 32}33 34/******************************** repeatable execution   Required code END*******************************/35 #7, results and release 37 38//Release result $stmt->free_result (); 40//Off precompiled statement 41 $stmt->close (); 42//Close database connection $mysqli->close ();?> 

8. Other functions

(1 Gets the number of rows and columns Num_rows Field_count

(2 Gets a column of the result set: Header for example

$result = $mysqli->query ();

$result->fetch_field ();

(3 Remove Data

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

Each data is then fetched through the foreach ($row as $val) {}

PHP mysqli Extension Library (Object-oriented/database operations encapsulation/transaction control/precompilation)