Implementation method of php manipulating mysqli database

From php5From version .0, mysql(i) support was added, and all new features are added in the form of objects

i stands for improvement, meaning more features, higher efficiency, and stability

Compilation parameters:

./configure --with-mysql=/usr/bin/mysql_config \ #Use Mysql ClientLibrary (libmysql) to build
--with-mysqli=mysqlnd \ #Use Mysql Native Dirver i.e. mysqlnd
--with-pdo-mysql=mysqlnd #Use Mysql Native Dirver i.e. mysqlnd

Due to copyright issues, from php5.3Start php to start using mysqlnd instead of libmysql.dll　
mysqlnd is a mysql database driver developed by zend company, which has improved in all aspects compared to the original

#Use mysqlnd compilation

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

Both procedural and object-oriented methods are supported by mysqli

Three classes provided by mysqli:

1and mysqli related to connections
2and MySQLi_Result processing result set
3and mysqli_stmt preprocessing class

#Set character set
set_charset

#Get character set
character_set_name

Get database object

//Method to create mysqli object 1
//Block connection errors
$mysqli = new mysqli('127.0.0.1', 'root', '', 'test');
//Can only use functions to judge whether the connection is successful
if(mysqli_connect_errno())
{
  echo mysqli_connect_error();
}
//Method to create mysqli object 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: Failure returns false, select returns result set object, others return true non false, indicating that the sql execution was successful

No result set example

$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', 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 rows
ee($mysqli->affected_rows);
# Inserted id
ee($mysqli->insert_id);
ee($mysqli);

There is a 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);
}
# Number of rows
ee($result->num_rows);
# Number of columns
ee($result->field_count);
# Number of fields
ee($result->field_count);
# Get all field information
$field_arr = $result->fetch_fields();
# Move field pointer
// $result->field_seek(1);
# Get field information one by one
while($field = $result->fetch_field())
{
  ee($field);
}
# Move record pointer
$result->data_seek(1);
# Get all data at once
$data = $result->fetch_all(MYSQLI_ASSOC);
# Get result set in associative array
$data = array();
$result->data_seek(0); # Reset pointer to the start
while($row = $result->fetch_assoc())
{
  $data[] = $row;
}
ee($data);
$result->free();
$mysqli->close();

Execute multiple statements at once multiquery (not recommended for use)

No result set, at this time affected_rows can only get the last affected row count

$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(
   1, 2)    
   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();

There is a 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 * 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();#Get the result set at the current cursor position
  $data = $result->fetch_all();
  ee($data);
})while($mysqli->next_result());#Move 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='zhangsan'";
  $result=$mysqli->query($sql);
  if(!$result){
    $error=false;
    echo "Transfer failed from Zhangsan"
";
  }else{
    if($mysqli->affected_rows==0){
      $error=false;
      echo "Zhangsan's money did not change";  
    }else{
      echo "Transfer successful from Zhangsan's account!"
";
    }
  }
  $sql="update zh set ye=ye+{$price} where name='lisi1'";
  $result=$mysqli->query($sql);
  if(!$result){
    $error=false;
    echo "Transfer from Lisi failed"
";
  }else{
    if($mysqli->affected_rows==0){
      $error=false;
      echo "Lisi's money did not change";  
    }else{
      echo "Transfer successful to Lisi's account!"
";
    }
  }
  if($error){
    echo "Transfer successful!";
    $mysqli->commit();
  }else{
    echo "Transfer failed!";
    $mysqli->rollback();
  }
  $mysqli->autocommit(1);
  $mysqli->close();

mysqli_stmt: mysqli prepared statement class (recommended): Represents a prepared statement, which is compiled only once on the server side

mysqli and mysqli_result can achieve the same function

Advantages:High efficiency, suitable for situations where the statements are the same but the data is different, and it can prevent the occurrence of SQL injection

mysqli_stmt example: Non-select statement

require 'fns.php';
//Method to create mysqli object 
$mysqli = @new mysqli('127.0.0.1', 'root', '', 'test');
//Can only use functions to judge 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 but different values
//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, 1000);
  $stmt->execute();
}
//Last id
ee($stmt->insert_id);
//Number of affected rows Note: the last executed
ee($stmt->affected_rows);
//Error number
ee($stmt->errno);
//Error information
ee($stmt->error);
//More information can be seen in the stmt object
ee($stmt);
eee($mysqli);

mysqli_stmt example: select statement 1　

require 'fns.php';
//Method to create mysqli object 
$mysqli = @new mysqli('127.0.0.1', 'root', '', 'test');
//Can only use functions to judge whether the connection is successful
if(mysqli_connect_errno())
{
  echo mysqli_connect_error();
  die;
}
$mysqli->set_charset('utf8);
$sql = "select * from limove where id

This article about the implementation method of php manipulating mysqli database is all the content that the editor shares with everyone. I hope it can give everyone a reference, and I also hope that everyone will support and cheer for the tutorial.