MYSQLI Operating Database

Source: Internet
Author: User
Tags set time stmt

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);//Set time-out $mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ' test '); $sql = "INSERT into Limove (' name ', ' order ') VALUES (' AA ', ' one-one '); $rst = $mysqli->query ($sq l); $sql = "Delete from Limove where id = 221"; $rst = $mysqli->query ($sql); if ($rst = = = False) {    ee ($mysqli->errno) ;    EE ($mysqli->error);} #影响条数ee ($mysqli->affected_rows); #插入的idee ($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 operation Database (RPM)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.