Php manipulated mysqli database and mysqli Database

Php manipulated mysqli database and mysqli Database

Mysql (I) support has been added since php5.0. Newly Added functions are added as objects.

I indicates that there are many functions, high efficiency, and stability to be improved.

Compile-time parameters:

. /Configure -- with-mysql =/usr/bin/mysql_config \ # use Mysql ClientLibrary (libmysql) build -- with-mysqli = mysqlnd \ # use Mysql Native Dirver or mysqlnd -- with-pdo-mysql = mysqlnd # use Mysql Native Dirver or mysqlnd

Due to copyright issues, php began to replace libmysql. dll with mysqlnd since php5.3.
Mysqlnd is the mysql database driver developed by zend, which is better than the previous one.

# Compile with mysqlnd

./Configure -- with-mysql = mysqlnd -- with-mysqli = mysqlnd -- with-pdo-mysql = mysqlnd add your Parameter

Support for mysqli process and object Methods

Mysqli provides three classes:
1. mysqli and connection-related
2. MySQLi_Result processing result set
3. mysqli_stmt preprocessing class

# Set character sets
Set_charset

# Retrieving character sets
Character_set_name

Obtain database objects

// Create mysqli object method 1 // block connection errors $ mysqli = new mysqli ('2017. 0.0.1 ', 'root', '', 'test'); // you can only use a function to determine whether the connection is successful. if (mysqli_connect_errno () {echo mysqli_connect_error ();} // method 2 for creating a mysqli object you can set some parameters $ mysqli = mysqli_init (); $ mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 2 ); // set the timeout value $ mysqli-> real_connect ('2017. 0.0.1 ', 'root', '', 'test ');Query: If the query fails, false is returned. If the select statement succeeds, the result set object is returned. If the other statements return true, rather than false, the SQL statement is successfully executed.

Example of no result set

$ Mysqli = mysqli_init (); $ mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 2); // you can specify the timeout value for $ mysqli-> real_connect ('123. 0.0.1 ', 'root', '', 'test'); $ SQL =" insert into limove ('name', 'order') values ('A', 11) "; $ rst = $ mysqli-> query ($ SQL); $ SQL =" delete from limove where id = 221 "; $ rst = $ mysqli-> query ($ SQL); if ($ rst = false) {ee ($ mysqli-> errno ); ee ($ mysqli-> error) ;}# Number of affected items ee ($ mysqli-> affected_rows); # inserted idee ($ mysqli-> insert_id); ee ($ mysqli );  

Result set

 

$ Mysqli = mysqli_init (); $ mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 2); // you can specify the timeout value for $ mysqli-> real_connect ('123. 0.0.1 ', 'root', '', 'test'); $ SQL =" select * from limove as limove_as "; $ result = $ mysqli-> query ($ SQL ); if ($ result = false) {ee ($ mysqli-> errno); ee ($ mysqli-> error);} # number of lines ee ($ result-> num_rows ); # Number of columns ee ($ result-> field_count); # number of fields ee ($ result-> field_count); # obtain information about all fields $ field_arr = $ result-> fetch_fields (); # Move the pointer to a field // $ result-> field_seek (1); # obtain the field information in sequence while ($ field = $ result-> fetch_field ()) {ee ($ field) ;}# move record pointer $ result-> data_seek (1); # Get all data at a time $ data = $ result-> fetch_all (MYSQLI_ASSOC ); # obtain the result set by associating an array $ data = array (); $ result-> data_seek (0 ); # reset the pointer to the starting while ($ row = $ result-> fetch_assoc () {$ data [] = $ row;} ee ($ data ); $ result-> free (); $ mysqli-> close ();Multi-statement multiquery execution at a time (not recommended)

No result set. In this case, affected_rows can only obtain the number of affected items.

$ Mysqli = mysqli_init (); $ mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 2); // you can specify the timeout value for $ mysqli-> real_connect ('123. 0.0.1 ', 'root', '', 'test'); $ SQL _arr = array ('insert into limove (id, 'name', 'order') values (null, 1, 2) ', 'insert into limove (id, 'name', 'order') values (null, 1,222 )', 'delete from limove where 'order' = 2',); $ SQL = implode (';', $ SQL _arr); $ result = $ mysqli-> multi_query ($ SQL ); if ($ result = false) {ee ($ mysqli-> errno); ee ($ mysqli-> error) ;}$ mysqli-> close (); 

Result set

$ Mysqli = mysqli_init (); $ mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 2); // you can specify the timeout value for $ mysqli-> real_connect ('123. 0.0.1 ', 'root', '', 'test'); $ SQL _arr = array ('show tables', 'desc select * from limove ', 'Show create table limove ',); $ SQL = implode ('; ', $ SQL _arr); $ rst = $ mysqli-> multi_query ($ SQL ); if ($ rst = false) {ee ($ mysqli-> errno); ee ($ mysqli-> error );} do {$ result = $ mysqli-> store_result (); # obtain the result set of the current cursor $ data = $ result-> fetch_all (); ee ($ data );} while ($ mysqli-> next_result (); # move the cursor to the next result set $ mysqli-> close (); 

Transaction processing:

$ Mysqli = new mysqli ("localhost", "root", "123456", "xsphpdb"); // transaction processing $ mysqli-> autocommit (0 ); $ error = true; $ price = 50; $ SQL = "update zh set ye = ye-{$ price} where name = 'hangsan '"; $ result = $ mysqli-> query ($ SQL); if (! $ Result) {$ error = false; echo "transfer from Michael Jacob failed
";}Else {if ($ mysqli-> affected_rows == 0) {$ error = false; echo" zhangsan's money has not changed ";} else {echo "transferred from Michael Jacob's account!
";}}$ SQL =" update zh set ye = ye + {$ price} where name = 'lisi1' "; $ result = $ mysqli-> query ($ SQL ); if (! $ Result) {$ error = false; echo "failed to transfer from Li Si
";}Else {if ($ mysqli-> affected_rows == 0) {$ error = false; echo" Li Si's money has not changed ";} else {echo "successfully transferred to account Li Si!
";}} If ($ error) {echo" transfer successful! "; $ Mysqli-> commit ();} else {echo" Transfer failed! "; $ Mysqli-> rollback ();} $ mysqli-> autocommit (1); $ mysqli-> close (); 

Mysqli_stmt: mysqli preprocessing class (recommended): indicates a prepared statement. The server only compiles the SQL statement once.
Use mysqli and mysqli_result to implement the same function
Advantage: high efficiency, suitable for cases where the same statement is only different from the data, can prevent the generation of SQL Injection

 

Example of mysqli_stmt: Non-select statement

Require 'fns. php '; // create a mysqli object using $ mysqli = @ new mysqli ('2017. 127. 0.0.1 ', 'root', '', 'test'); // you can only use a function to determine whether the connection is successful. if (mysqli_connect_errno () {echo mysqli_connect_error (); die ;} $ mysqli-> set_charset ('utf8'); $ SQL = "insert into limove values (?, ?, ?) "; // The same statement value is different. // a direct method in mysqli can be used $ stmt = $ mysqli-> prepare ($ SQL ); // bind_param ('iss', $ id, $ name, $ order); for ($ I = 0; $ I <5; $ I ++) {$ id = 0; $ name = 'name'; $ order = mt_rand (1, 1000); $ stmt-> execute ();} // The Last idee ($ stmt-> insert_id); // The number of affected rows. Note: The last execution ee ($ stmt-> affected_rows ); // error code ee ($ stmt-> errno); // error message ee ($ stmt-> error ); // you can see more information in the stmt object ee ($ stmt); eee ($ mysqli ); 

 Example of mysqli_stmt: select statement 1

Require 'fns. php '; // create a mysqli object using $ mysqli = @ new mysqli ('2017. 127. 0.0.1 ', 'root', '', 'test'); // you can only use a function to determine whether the connection is successful. if (mysqli_connect_errno () {echo mysqli_connect_error (); die ;} $ mysqli-> set_charset ('utf8'); $ SQL = "select * from limove where id