The implementation method of PHP manipulating Mysqli database _php example

Add MySQL (i) support from php5.0, and new features are added in the form of objects

I express the meaning of the improvement of more functions, high efficiency, stability

Compile-time parameters:

./configure--with-mysql=/usr/bin/mysql_config \ #使用 mysql clientlibrary (libmysql) build
--with-mysqli=mysqlnd \ #使用 MySQL Native dirver that mysqlnd
--with-pdo-mysql=mysqlnd #使用 mysql Native dirver that 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 Company, which has been improved in comparison with all aspects.

#使用mysqlnd编译

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

Mysqli procedures, object methods are supported

The MYSQLI offers three classes:

1, mysqli and connection-related
2, Mysqli_result processing result set
3, mysqli_stmt pretreatment class

#设置字符集
Set_charset

#获取字符集
Character_set_name

Getting database objects

Create Mysqli Object Mode 1
//Mask connection error generated
$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 ();
}

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 successful return of the result set object, others return true not false, meaning that SQL execution succeeded

No result set sample

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');

$sql = "INSERT into Limove (' name ', ' order ') VALUES (' AA ', one)";
$rst = $mysqli->query ($sql);

$sql = "Delete from Limove where id = 221";
$rst = $mysqli->query ($sql);

if ($rst = = False)
{
  ee ($mysqli->errno);
  EE ($mysqli->error);
}

#影响条数
ee ($mysqli->affected_rows);
#插入的id
ee ($mysqli->insert_id);

EE ($mysqli);

Have result set

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$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 a time multiquery (not recommended)

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

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');


$sql _arr = Array (
  ' INSERT into Limove (ID, ' name ', ' order ') VALUES (NULL, 1, 2) ',    
  ' INSERT into Limove (ID, ' name ', ' or Der ') 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 ();

Have result set

$mysqli = Mysqli_init ();
$mysqli->options (Mysqli_opt_connect_timeout, 2);/Set timeout time
$mysqli->real_connect (' 127.0.0.1 ', ' root ', ', ', ') ' Test ');

$sql _arr = Array (
  ' show Tables ',    
  ' desc select * To 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 (); #获取当前光标所在的结果集
  
  $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 en set ye=ye-{$price} where name= ' Zhangsan '";

  $result = $mysqli->query ($sql);
    if (! $result) {$error =false;
  echo "from Zhang San to fail";
      }else{if ($mysqli->affected_rows==0) {$error =false;  
    echo "John's money has not changed";
    }else{echo "from the John account to relay success!";

  } $sql = "update en set ye=ye+{$price} where name= ' Lisi1 '";

  $result = $mysqli->query ($sql);
    if (! $result) {$error =false;
  echo "Transferred from Dick to failure";
      }else{if ($mysqli->affected_rows==0) {$error =false;  
    echo "Dick's money has not changed";
    }else{echo "to Lee four account into success!";
    } if ($error) {echo "Transfer successful!";
  $mysqli->commit ();
    }else{echo "Transfer failed!";
  $mysqli->rollback ();
  } $mysqli->autocommit (1);
$mysqli->close (); 

Mysqli_stmt:mysqli preprocessing Class (recommended): a prepared statement that the server side compiles only once SQL

The same functionality can be achieved with mysqli and Mysqli_result

Advantages: high efficiency, suitable for statements with the same data only a different situation, can prevent the generation of SQL injection

mysqli_stmt Example: Non-SELECT statement

Require ' fns.php ';

Create a Mysqli object way 
$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 = "INSERT into limove values (?,?,?)"; The statement has the same value



//mysqli there are direct methods available
$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, 1000);
  $stmt->execute ();

}

Last ID
ee ($stmt->insert_id);

Number of rows affected: The last performed
ee ($stmt->affected_rows);

Error number
EE ($stmt->errno);

Error message
ee ($stmt->error);

More information
ee ($stmt) can be seen in the stmt object;

EEE ($MYSQLI);

MYSQLI_STMT Example: SELECT statement 1

Require ' fns.php ';

Create a Mysqli object way 
$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

The above PHP manipulation Mysqli Database Implementation method is small series to share all the content, hope to give you a reference, but also hope that we support cloud habitat community.