MySQL database interaction with PHP Mysqli extension

[Related learning recommendations: php programming (video)]

Introduction

I have briefly introduced how to install MySQL locally and interact with the MySQL server through the command line and GUI client software.

You can interact with MySQL through commands on the command line, and you can interact with MySQL through the graphical interface on the client software. So how to establish a connection and interaction with MySQL in a PHP program? In fact, we can regard the PHP application as a client of the MySQL server, and then interact with the MySQL server through the API provided by the encapsulated PHP extension package, just like we do in the command line and client software , but now this interaction is changed from manual operation to completed by writing corresponding PHP code.

PHP MySQLi extension

PHP officially provides many extensions for interacting with the MySQL server, from the earliest mysql to the later enhanced version of mysqli (more secure), they are all PHP functions Extension packages in the era of formal programming. Generally speaking, the local PHP integrated development environment will come with mysqli extension:

Let’s use a simple example below To demonstrate how to interact with the MySQL server through the mysqli extension.

Database connection and query

Sample code

Add a new mysql in the php_learning directory subdirectory, then create a new mysqli.php file in the subdirectory, and write a piece of code to establish a database connection and query through the mysqli extended API:

<?php $host = &#39;127.0.0.1&#39;;   // MySQL 服务器主机地址
$port = 3306;          // MySQL 服务器进程端口号
$user = &#39;root&#39;;         // 用户名
$password = &#39;root&#39;;     // 密码
$dbname = &#39;test&#39;;       // 使用的数据库名称

// 通过 mysqli 扩展建立与 mysql 服务器的连接
$conn = mysqli_connect($host, $user, $password, $dbname, $port);

// 在连接实例上进行查询
$sql = &#39;SELECT * FROM `post`&#39;;
$res = mysqli_query($conn, $sql);

// 获取所有结果
$rows = mysqli_fetch_all($res);
var_dump($rows);

// 释放资源
mysqli_free_result($res);
// 关闭连接
mysqli_close($conn);

Yes As you can see, the connection to the MySQL database can be established through the mysqli_connect function. We pass in 5 parameters, which are database host, user name, password, database name and port number. After the connection is successfully established, you can hold the connection. The instance executes the database query through the mysqli_query function. We pass in the SQL statement as the second parameter. The return result of the function is a query result set instance. After getting this instance, you can use mysqli_fetch_* The series function obtains the result data.

Here we get all the query results through the mysqli_fetch_all function, and start the PHP built-in HTTP server through php -S localhost:9000:

You can print the query results in the browser through http://localhost:9000/mysql/mysqli.php:

Optimize the rendering effect

At this time, the readability of the page style is very poor. You can insert a section of echo '<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">'# before printing the output result in the source code. ## Code optimization rendering effect: </pre><div class="contentsignin">Copy after login</div></div>

// 获取所有结果
$rows = mysqli_fetch_all($res);
echo '<pre class="brush:php;toolbar:false">';
var_dump($rows);

Refresh the browser page, you can see the following printing effect:

Related learning recommendations:

mysql tutorial(Video)

Set character encoding

There is a small problem here, that is, the Emoji emoticons are not displayed properly. The characters are garbled. We can set the character encoding to

utf8mb4 through the mysqli_set_charset function just like setting the default character encoding on the command line:

// 通过 mysqli 扩展建立与 mysql 服务器的连接
$conn = mysqli_connect($host, $user, $password, $dbname, $port);
// 设置字符编码为 utf8mb4
mysqli_set_charset($conn, 'utf8mb4');

...

// 获取所有结果
$rows = mysqli_fetch_all($res);
echo '<pre class="brush:php;toolbar:false">';
var_dump($rows[2]);

Refresh the page and you can see the Emoji expression:

Return to associative array

The currently returned result is an index array, and the field name corresponding to the value cannot be known. To obtain the complete field Name and field value mapping can be achieved by setting the second parameter value passed into the

mysqli_fetch_all function to MYSQLI_ASSOC (the default is MYSQLI_NUM):

// 获取所有结果(关联数组)
$rows = mysqli_fetch_all($res, MYSQLI_ASSOC);
echo '<pre class="brush:php;toolbar:false">';
var_dump($rows[2]);

Return a single result

All the results returned above are multiple results (even if only one record is returned, the returned result is a multi-dimensional array), Sometimes, we only want to return the first result in the result set. This can be achieved through the mysqli_fetch_row function:

// 在连接实例上进行查询
$sql = 'SELECT * FROM `post` WHERE id = 1';
$res = mysqli_query($conn, $sql);

// 获取所有结果
/*
$rows = mysqli_fetch_all($res, MYSQLI_ASSOC);
echo '<pre class="brush:php;toolbar:false">';
var_dump($rows);*/

// 获取单条结果
$row = mysqli_fetch_row($res);
echo '<pre class="brush:php;toolbar:false">';
var_dump($row);

Refresh the browser test page and print the results as follows:

可以看到返回结果已经是一个一维数组了，只包含一条记录。如果想要返回关联数组结果，需要通过一个新的函数 mysqli_fetch_assoc 函数来实现：

// 获取单条结果
// $row = mysqli_fetch_row($res);
$row = mysqli_fetch_assoc($res);
echo '<pre class="brush:php;toolbar:false">';
var_dump($row);

对应的打印结果如下：

将返回结果映射到指定对象

除了返回数组格式结果外，还可以借助 mysqli_fetch_object 函数将数据库查询结果映射到指定对象实例并返回：

class Post
{
    public $id;
    public $title;
    public $content;
    public $created_at;

    public function __toString()
    {
        return '[#' . $this->id . ']' . $this->title;
    }
}
// 将数据库返回结果映射到指定个对象
$post = mysqli_fetch_object($res, Post::class);
echo $post;

对应的打印结果如下，说明对象映射成功（调用了对象的魔术方法 __toString 打印输出该对象）：

避免 SQL 注入攻击

在上述数据库查询操作中，我们直接将原生 SQL 语句传递给 MySQL 数据库执行，如果 SQL 语句中包含了用户传递的参数，则存在 SQL 注入风险，要避免 SQL 注入攻击，在 mysqli 扩展中，可以通过构建预处理语句的方式实现：

1. 首先通过 mysqli_prepare 函数构建包含占位符（替代具体参数值）的预处理 SQL 语句；
2. 然后通过 mysqli_stmt_bind_param 函数将参数值绑定到预处理语句；
3. 最后通过 mysqli_stmt_execute 函数执行填充参数值之后的完整 SQL 语句，由于底层做了转化处理，所以这时候执行的 SQL 语句不存在 SQL 注入风险。

下面，我们以插入记录到数据库为例，演示如何通过预处理语句的方式与数据库交互，提高代码安全性。

插入记录到数据库

我们首先基于预处理语句编写插入记录到数据库的代码如下（基于上面的 $conn 连接实例）：

// 插入记录到数据库
$sql = 'INSERT INTO `post` (title, content, created_at) VALUES (?, ?, ?)';
// 构建预处理 SQL 语句
$stmt = mysqli_prepare($conn, $sql);

// 绑定参数值
$title = '这是一篇测试文章';
$content = '测试文章啊啊啊

The above is the detailed content of MySQL database interaction with PHP Mysqli extension. For more information, please follow other related articles on the PHP Chinese website!