Interacting with MySQL database through PDO extension to achieve basic use

Related learning recommendations: mysql tutorial, php programming

## on In this tutorial, I introduced you how to interact with the MySQL database through PHP's built-in Mysqli extension. Today we will take a look at another PHP built-in database extension - PDO, whose full name is PHP Data Objects, that is, PHP data objects.

PDO Introduction and Installation

PDO defines a lightweight consistent interface for PHP to access the database, so it provides a data access abstraction layer and does not itself implement any database interaction functions. , you must use a PDO driver of a specific database to access database services. These specific database drivers include MySQL, PostgreSQL, SQLite, SQL Server, Oracle, etc. Therefore, PDO is a more standard and more general database extension, and it is completely object-oriented. At present, PDO has become the basic component for building database interaction in various mainstream PHP frameworks, including the Laravel framework, which also provides database access functions based on PDO extensions.

The PHP local integrated development environments we previously recommended all integrate support for PDO extensions (the following are Laragon's pre-installed PHP extensions):

And It can be seen that in addition to the

PDO extension, there is also a matching specific database extension pdo_mysql. Only with this specific extension can the MySQL database be accessed. In addition, PDO provides a unified database access interface, so specific database extensions that implement the PDO interface can access the database in exactly the same way. In this way, if the application needs to switch databases, there is no need to reconstruct the database operation code. Based on this alone, you can throw away the MySQLi extension and embrace PDO.

If you have installed Laradock, you can start the

workspace container, and then view its pre-installed PHP extension. You can see that it contains more specific PDO database driver support:

pdo_pgsql represents the PostgresSQL database driver, pdo_sqlite represents the SQLite database driver.

Establishing database connection and basic query

In our tutorial, we still choose to use MySQL as an example for demonstration.

To establish a database connection through PDO extension, just instantiate the PDO object directly. We write a simple sample code as follows (create

pdo.php in the php_learning/mysql directory Storage code):

<?php

// 设置连接属性
$dsn = &#39;mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8mb4&#39;;
$user = &#39;root&#39;;
$pass = &#39;root&#39;;

try {
    // 建立连接
    $pdo = new PDO($dsn, $user, $pass);
    // 执行 SQL 查询
    $sql = $pdo->quote(&#39;SELECT * FROM `post` ORDER BY `id` DESC&#39;);
    $res = $pdo->query($sql);
    // 打印查询结果
    echo &#39;<pre class="brush:php;toolbar:false">&#39;;
    foreach ($res as $row) {
        print_r($row);
    }
} catch (PDOException $exception) {
    // 如果数据库操作出现异常，则捕获并打印
    printf("Error: %s\n", $exception->getMessage());
} finally {
    // 释放 PDO 连接实例
    $pdo = null;
}

When instantiating a PDO object to create a database connection, at least three string type parameters need to be passed in. The first parameter contains database host information, such as database driver type ( Here is

mysql), IP address (the local address is 127.0.0.1), port number (optional, the default is 3306), and the name of the database to be connected (test), character encoding information (utf8mb4), etc. The second parameter is the user name, and the third parameter is the password.

Next, we can call the query method on the PDO object instance

$pdo to execute the specified SQL statement to interact with the database (add, delete, modify, check), but before that, we first The quote method is called to escape the query SQL statement. The function of this function is similar to mysqli_escape_string.

After executing the SQL query, a query result set will be returned, which can be printed through loop traversal.

In addition, if an error occurs during database connection and query operations through PDO extension, an exception will be thrown. In order to increase the robustness of the program, we are using

try...catch... The statement captures the PDOException exception, prints the error message, and finally releases the PDO object instance through the finally statement, because this statement block will be executed regardless of whether an exception is thrown.

Run

php -S localhost:9000 Start the built-in HTTP server for testing, visit http://localhost:9000/mysql/pdo.php in the browser , you can see the normal printing result:

Let’s modify the connection information in the above code, for example, adjust the password value to

test, this The PDO exception will be caught and the error message will be printed:

Note: The IP address in the error message is the IP address inside the MySQL Docker container. It can be ignored. This error is caused by a wrong password, not an IP address problem.

If you want to know more related articles, please pay attention to the php mysql column!

The above is the detailed content of Interacting with MySQL database through PDO extension to achieve basic use. For more information, please follow other related articles on the PHP Chinese website!