MySQLI method of operating MySQL database

不言
Release: 2023-03-29 21:46:02
Original
2836 people have browsed it

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’)
Copy after login

2)Execute SQL statement

$mysqli -> query($sql);
Copy after login

3) Get the result

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

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

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();
Copy after login

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);
Copy after login

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)
Copy after login

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

2) Transaction rollback

$mysqli -> rollback()
Copy after login

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

3) Transaction submission

$mysqli -> commit()
Copy after login

4) Close the connection

$mysqli -> close()
Copy after login

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
Copy after login

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 '操作失败';
}
Copy after login

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)
Copy after login

3) Parameter assignment

$stmt -> bind_param(&#39;参数类型&#39;, 参数1, 参数2...)
Copy after login

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()
Copy after login

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()
Copy after login

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

$stmt->store_result()
Copy after login

Please see the link below for details

Detailed analysis

7) Get the results of each record

$stmt ->fetch()
Copy after login

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);
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template