MYSQLI Operating Database

Added MySQL (i) support starting from php5.0, adding new functions as objects

I means improved meaning function, high efficiency, stable

Compile-time parameters:

./configure--with-mysql=/usr/bin/mysql_config \ #使用 mysql clientlibrary (libmysql) build--with-mysqli=mysqlnd \ #使用 MySQL Native Dirver is mysqlnd--with-pdo-mysql=mysqlnd #使用 mysql Native dirver is mysqlnd

Since the copyright issue began with php5.3 PHP began to replace Libmysql.dll with Mysqlnd
MYSQLND is a MySQL database driver developed by Zend and has been improved compared to the original

#使用mysqlnd编译

./configure--with-mysql=mysqlnd--with-mysqli=mysqlnd--with-pdo-mysql=mysqlnd plus your parameters

MYSQLI process, object mode all support

Three classes provided by Mysqli:
1. Mysqli and Connection related
2. Mysqli_result Processing result set
3. mysqli_stmt Pretreatment class

#设置字符集
Set_charset

#获取字符集
Character_set_name

Get database objects

Create Mysqli Object Mode 1//Mask connection Error $mysqli = new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');//function only to determine if the connection succeeds if (mysqli_connect_ errno ()) {    echo mysqli_connect_error ();} Create Mysqli Object Mode 2 You can set some parameters $mysqli = Mysqli_init (), $mysqli->options (Mysqli_opt_connect_timeout, 2);//Set timeout time $ Mysqli->real_connect (' 127.0.0.1 ', ' root ', ' ', ' test ');

Query: Failed to return false,select successfully returned result set object, other return true not false, meaning SQL execution succeeded

No result set Example

$mysqli=Mysqli_init();$mysqli->options (Mysqli_opt_connect_timeout, 2);//setting the time-out period$mysqli->real_connect (' 127.0.0.1 ', ' root ', ' ', ' test '));$sql= "INSERT into Limove (' name ', ' order ') VALUES (' AA ', 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 barsEe$mysqli-affected_rows);#ID of the insertEe$mysqli-insert_id); EE ($mysqli);

Have result set

$mysqli = Mysqli_init (); $mysqli->options (Mysqli_opt_connect_timeout, 2);//Set time-out $mysqli->real_connect (' 127.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);} #行数ee ($result->num_rows); #列数ee ($result->field_count); #字段个数ee ($result->field_count); #获取所有字段的信息 $field _ arr = $result->fetch_fields (); #移动字段的指针//$result->field_seek (1); #依次获取字段的信息while ($field = $result->fetch_ Field ()) {    ee ($field);} #移动记录指针 $result->data_seek (1), #一次获取所有数据 $data = $result->fetch_all (MYSQLI_ASSOC); #关联数组方式获取结果集 $data = Array (); $ Result->data_seek (0); #重置指针到起始while ($row = $result->fetch_assoc ()) {    $data [] = $row;} EE ($data); $result->free (); $mysqli->close ();

Execute multiple statements at once Multiquery (deprecated)

No result set, at which point the affected_rows can only get the last number of bars affected

$mysqli = Mysqli_init (); $mysqli->options (Mysqli_opt_connect_timeout, 2);//Set time-out $mysqli->real_connect (' 127.0.0.1 ', ' root ', ' ', ' test '), $sql _arr = array (    ' INSERT into Limove (ID, ' name ', ' order ') VALUES (NULL, 1, 2) ',            ' I Nsert into Limove (ID, ' name ', ' order ') VALUES (NULL, 1, 222) ',            ' delete from Limove where ' order ' = 2 ',        ); $sql = Impl Ode ('; ', $sql _arr); $result = $mysqli->multi_query ($sql); if ($result = = = False) {    ee ($mysqli->errno);    EE ($mysqli->error);} $mysqli->close ();

Have result set

$mysqli = Mysqli_init (); $mysqli->options (Mysqli_opt_connect_timeout, 2);//Set time-out $mysqli->real_connect (' 127.0.0.1 ', ' root ', ' ', ' test '); $sql _arr = Array (    ' show Tables ',            ' desc select * from Limove ',            ' show Create tab Le Limove ',        ); $sql = implode ('; ', $sql _arr); $rst = $mysqli->multi_query ($sql); if ($rst = = = False) {    EE ($ Mysqli->errno);    EE ($mysqli->error);} do{    $result = $mysqli->store_result (), #获取当前光标所在的结果集        $data = $result->fetch_all ();        EE ($data);    } while ($mysqli->next_result ()); #光标移动到下一个结果集 $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= ' Zhangsan '";    $result = $mysqli->query ($sql);        if (! $result) {$error =false;    echo "From Zhang three turn out failure <br>";            }else{if ($mysqli->affected_rows==0) {$error =false;            Echo "Zhang San's money has not changed";        }else{echo "from the Zhang San account transfer success!<br>";    }} $sql = "Update zh set ye=ye+{$price} where name= ' Lisi1 '";    $result = $mysqli->query ($sql);        if (! $result) {$error =false;    echo "Transfer from John Doe to failure <br>";            }else{if ($mysqli->affected_rows==0) {$error =false;            echo "John Doe's money has not changed";        }else{echo "to Li four account into the success of!<br>";        }} if ($error) {echo "Transfer succeeded!";    $mysqli->commit ();        }else{echo "Transfer failed!";    $mysqli->rollback ();    } $mysqli->autocommit (1); $mysqlI->close (); 

Mysqli_stmt:mysqli preprocessing Class (recommended): Represents a prepared statement, the server side compiles only once SQL
The same functionality can be achieved with mysqli and Mysqli_result
Advantages: High efficiency, applicable to the same statement is only the case of different data, can prevent the generation of SQL injection

mysqli_stmt Example: Non-SELECT statement

Require  ' fns.php ';//Create Mysqli object Mode $mysqli = @new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');//Only function is used to determine if the connection succeeds if (mysqli _connect_errno ()) {    echo mysqli_connect_error ();    Die;} $mysqli->set_charset (' UTF8 '); $sql = "INSERT into limove values (?,?,?)"; The same value as the statement/*//gets the stmt object $stmt = $mysqli->stmt_init ();//prepare an SQL statement to be placed on the server side $stmt->prepare ($sql); */// There are direct methods available in mysqli $stmt = $mysqli->prepare ($sql);//Binding Parameters $stmt->bind_param (' ISS ', $id, $name, $order); for ($i =0; $i <5; $i + +) {    $id = 0;    $name = ' name ';    $order = Mt_rand (1, +);    $stmt->execute ();} Last Idee ($stmt->insert_id);//The number of rows affected note: The last one executed by the EE ($stmt->affected_rows);//Error number EE ($stmt->errno);//error information EE ($ Stmt->error);//stmt object can see more information ee ($stmt); Eee ($MYSQLI);

mysqli_stmt Example: SELECT statement 1

Require  ' fns.php ';//Create Mysqli object Mode $mysqli = @new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');//Only function is used to determine if the connection succeeds if (mysqli _connect_errno ()) {    echo mysqli_connect_error ();    Die;} $mysqli->set_charset (' UTF8 '); $sql = "SELECT * from Limove where id<?"; $stmt = $mysqli->prepare ($sql), $stmt->bind_param (' i ', $i), $stmt->bind_result ($a, $b, $c), $i =40, $stmt Execute ();//Fetch the results $stmt->store_result ();//get field information $result = $stmt->result_metadata ();//can only get some field information while ($ field = $result->fetch_field ()) {    ee ($field->name);} $stmt->data_seek (2); The position of the pointer is moved only after the Store_result is executed ($stmt->fetch ()) {    ee ("{$a}|{ $b}| {$c} ");} The number of records recorded, the EE ($stmt->num_rows), EE ($stmt), $stmt->free_result (), $stmt->close (), will be effective only after the store_result is executed;

mysqli_stmt Example: SELECT statement 2

<?php require  ' fns.php ';//Create Mysqli object Mode $mysqli = @new mysqli (' 127.0.0.1 ', ' root ', ' ', ' test ');// Only functions can be used to determine if the connection succeeds if (Mysqli_connect_errno ()) {    echo mysqli_connect_error ();    Die;} $mysqli->set_charset (' UTF8 '); $sql = "SELECT * from Limove where id<?"; $stmt = $mysqli->prepare ($sql), $stmt->bind_param (' i ', $i), $stmt->bind_result ($a, $b, $c), $i =40, $stmt Execute ();//Get result $result = $stmt->get_result (); $data = $result->fetch_all (MYSQLI_ASSOC); EE ($data);d ebug ();

MYSQLI Operating Database