MySQLI method of operating MySQL database

This article mainly introduces the MySQLI method of operating MySQL database with PHP. It has certain reference value. Now I share it with you. Friends in need can refer to it.

Three ways to operate MySQL with PHP This method

MySQL

Non-permanent connection, every time the database is used, a new process will be opened,

Disadvantages:
The performance is low and will be abandoned after PHP5.0.

MySQLi

Permanent connection, reduce server pressure, only supports MySQL

Disadvantages:
Takes up memory

PDO ( Recommended)

can realize the common functions of MySQLi and supports most databases.

MySQLI method (object-oriented)

Basic usage:

1) Connect to the database

$mysqli = new mysqli(‘host’,‘username’,‘password’,‘database’)

2)Execute SQL statement

$mysqli -> query($sql);

3) Get the result

$result -> fetch_array([MYSQLI_ASSOC])     一个
$result -> fetch_assoc()                   一个

$result -> fetch_all([MYSQLI_ASSOC])       全部

The default method one is index array, after adding parameters it becomes associative array.

Method two is equivalent to method one plus parameters.

4) Close the connection

$mysqli -> close();

Example:

<?php

// MySQLi增删改例子

// MySQLi方式连接数据库
$mysqli = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;123456789&#39;,&#39;test&#39;);

// 执行SQL语句
// 使用utf-8编码
$mysqli -> query(&#39;set names utf8&#39;);

// 插入
//$result = $mysqli -> query("INSERT users(name,money) VALUES(&#39;甜筒&#39;,4) ");
//$result = $mysqli -> query("INSERT users(name,money) VALUES(&#39;麦辣鸡腿堡&#39;,16) ");

// 修改
//$result = $mysqli -> query("UPDATE users SET `name`= &#39;麦旋风&#39; WHERE `id` = 2");

// 删除
//$result = $mysqli -> query("DELETE FROM users WHERE `id` = 5");

//var_dump($result);

/*********************查询*****************/ 
$result = $mysqli -> query("SELECT * FROM users");
// 获取结果集
$data = $result -> fetch_all(MYSQLI_ASSOC);

var_dump($data);

Transaction control

A transaction is a database operation that combines multiple logical tasks into one execution unit (Total success or failure).

1) Open the transaction

$mysqli -> autocommit(false)

This method will cause the transaction to be submitted immediately, so the parameter false needs to be filled in.

2) Transaction rollback

$mysqli -> rollback()

If there is any error in the data, you can use this method to restore the data.

3) Transaction submission

$mysqli -> commit()

4) Close the connection

$mysqli -> close()

Note:

When we use query() to execute a SQL statement, he does not It will take care of the changes in the database and whether the SQL statement is wrong.

So we cannot rely on the return value of query() to determine whether the operation is successful or not.

To use the number of affected rows to determine:

$mysqli -> affected_rows

Instance

 query('set names utf8');


// 开启事务
$mysqli -> autocommit(false);

//SQL
$sql1 = "UPDATE users SET `money`=`money`+1  WHERE `id` = 1";
$sql2 = "UPDATE users SET `money`=`money`-1  WHERE `id` = 11";

// 执行SQL语句
$result = $mysqli -> query($sql1);
$r1 = $mysqli -> affected_rows;

$result = $mysqli -> query($sql2);
$r2 = $mysqli -> affected_rows;

if($r1 > 0 && $r2 > 0){
    // 提交事务
    $mysqli -> commit();
    echo '操作成功';
}else{
    // 事务回滚
    $mysqli -> rollback();
    echo '操作失败';
}

Preprocessing

Preprocessing, first submit the SQL statement to the server and execute it Precompiled.

When the client executes a SQL statement, it only needs to upload the input parameters;

If multiple reads or storage are involved, the efficiency is higher than that of ordinary SQL execution operations.

1) $sql = “???”

Parameter in the VALUE of the SQL statement is replaced by a question mark.

2) Create a precompiled object

 $stmt = $mysqli -> prepare($sql)

3) Parameter assignment

$stmt -> bind_param('参数类型', 参数1， 参数2...)

Parameter type description:
string-s int-i double-d bool-b

Special reminder:
Write parameter types in order without spaces or commas. For example, parameter 1 is string and parameter 2 is bool, written like this:
$stmt -> bind_param('si', parameter 1, parameter 2)

4) variable binding result set (this step is only required for queries , skip this step for other operations)

$stmt -> bind_result()

Special attention:
The variable of bind_result corresponds to the field name in the select statement, so be sure to pay attention to the quantity and order! ! !

5) Execute the code

$stmt -> execute()

6) Save the query record into the PHP program (query operation is optional)

$stmt->store_result()

Please see the link below for details

Detailed analysis

7) Get the results of each record

$stmt ->fetch()

Example

 query('set names utf8');

// SQL语句
$sql = "SELECT * FROM users WHERE id>? ";

// 创建预编译对象
$stmt = $mysqli -> prepare($sql);

// 参数绑定
$id = 1;
$stmt -> bind_param('i',$id);

// 绑定结果集   参数要和表的参数一致
$stmt -> bind_result($id,$name,$money,$age);

// 执行SQL语句
$stmt -> execute();

// 执行SQL语句
$stmt->store_result();

// 接收结果集
while($stmt -> fetch()){
    $data[] = [
        'id' => $id,
        'name' => $name,
        'money' => $money
    ];
}

var_dump($data);

Related recommendations:

PHP operation database: select operation operation

The above is the detailed content of MySQLI method of operating MySQL database. For more information, please follow other related articles on the PHP Chinese website!