Interaction with MySQL database through PDO extension (I): basic use

---

In the last tutorial, Mr. Xuejun introduced you how to interact with MySQL database through PHP's built-in MySQL extension. Today, let's take a look at another PHP built-in database extension, PDO, which is called PHP Data Objects in full.

PDO Introduction and Installation

PDO defines a lightweight consistent interface for PHP to access the database. Therefore, it provides a data access abstraction layer, and cannot implement any database interaction function itself. It must use a PDO driver of 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 general database extension, and it is completely object-oriented. At present, in various mainstream PHP frameworks, PDO has become the basic component for building database interaction, including the Laravel framework, and also provides database access based on PDO extensions.

The PHP local integrated development environment we previously recommended has integrated support for PDO extensions (the following are the PHP extensions preinstalled by Laragon):

And you can see that except PDO In addition to the extension, there is a specific database extension matching it pdo_mysql With this specific extension, you can access the MySQL database. In addition, PDO provides a unified database access interface, so the specific database extension that implements 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 restructure the database operation code. Only by this, you can throw away the MySQL i extension and enter the embrace of PDO.

If Laradock has been installed, you can start workspace Container, and then check its pre installed PHP extension. It can be seen that it contains more specific PDO database driver support:

among pdo_pgsql Represents a PostgresSQL database driver, pdo_sqlite Represents a SQLite database driver.

Establish database connection and basic query

In our tutorial, we still choose MySQL as an example.

To establish a database connection through PDO extension, directly instantiate the PDO object. We write a simple sample code as follows (in php_learning/mysql New under directory pdo.php Storage code):

 <? php //Set connection properties $dsn = 'mysql:host=127.0.0.1; port=3306;dbname=test;charset=utf8mb4'; $user = 'root'; $pass = 'root'; try { //Establish a connection $pdo = new PDO($dsn, $user, $pass); //Execute SQL Query $sql = $pdo->quote('SELECT * FROM `post` ORDER BY `id` DESC'); $res = $pdo->query($sql); //Print Query Results echo '<pre>'; foreach ($res as $row) { print_r($row); } } catch (PDOException $exception) { //If the database operation is abnormal, capture and print printf("Error: %s\n", $exception->getMessage()); } finally { //Release the PDO connection instance $pdo = null; }

When instantiating the PDO object to create a database connection, at least three string type parameters need to be passed in. The first parameter contains the database host information, such as the database driver type (here: mysql ）, IP address (local address is 127.0.0.1 ）, port number (optional, default is three thousand three hundred and six ）, Database name to connect to（ test ）Character encoding information（ utf8mb4 ）The second parameter is the user name, and the third parameter is the password.

Next, we can call the PDO object instance $pdo On query The method executes the specified SQL statement and interacts with the database (add, delete, modify, query), but before that, we first call quote Method to escape the query SQL statement. The 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 in database connection and query operations through PDO extension, an exception will be thrown. To increase the robustness of the program, we are using the try...catch... Statement capture PDOException Exception, and print the error message, and finally pass finally Statement releases the PDO object instance, because the statement block will execute regardless of whether an exception is thrown.

function php -S localhost:9000 Start the built-in HTTP server for testing and access it in the browser http://localhost:9000/mysql/pdo.php , you can see the normal print results:

Let's modify the connection information in the above code, such as adjusting the password value to test At this time, PDO exceptions will be caught and error messages will be printed:

Note: The IP address in the error message is the internal IP address of the MySQL Docker container. It can be ignored that this error is a password error, not an IP address problem.