How to use PDO extension in php?

In modern web application development, most require interaction with a database to store and retrieve data. In order to ensure the efficiency and security of interaction, developers often use PDO extensions (PHP Data Objects) as the database connection layer. This article will introduce you to how to use PDO extension to work with MySQL database.

Introduction to PDO

PDO is a universal way provided by PHP to access a variety of different databases. It allows us to use an object-oriented approach to connect to the database and execute queries. PDO supports multiple database types such as MySQL, SQLite, PostgreSQL and Oracle. Using PDO avoids hardcoding SQL statements and database access credentials in the code, which improves code portability and security.

Installing PDO

Before using PDO, you need to make sure that your PHP installation contains the PDO extension. You can check if your PHP supports PDO extensions using the following command:

php -m | grep -i pdo

If it outputs pdo_mysql and pdo_sqlite (or something similar), then your PHP installation already includes PDO extensions. If not, you'll need to do some configuration and installation work. You can view the specific installation at [https://www.php.net/manual/en/pdo.installation.php](https://www.php.net/manual/en/pdo.installation.php) step.

Connecting to MySQL database

Before using PDO to connect to the MySQL database, you need to prepare the connection information such as the host address, port number, user name and password required to connect to the data. Please make sure you have these credentials.

Before connecting to the MySQL database, you need to use the PDO class to instantiate a PDO object, as shown in the following code:

//连接到MySQL数据库，并返回PDO对象
try {
    $pdo = new PDO('mysql:host=主机地址;dbname=数据库名', '数据库用户名', '数据库密码');
} catch (PDOException $e) {
    die('连接失败: ' . $e->getMessage());
}

To connect to the MySQL database, you need to provide some Necessary information such as hostname, database name, username and password. This information will be passed to the PDO constructor, which will return a PDO object. If the connection fails, a PDOException will be thrown, and you need to use a try-catch block to catch the exception and handle the error.

Execute query

Before using PDO to execute a query, you need to create a PDOStatement object, which will represent a specific query and be used to obtain query results. Then, you can use the prepare() method to prepare a query statement, and use the execute() method to submit the query to the database, as shown in the following code:

try {
    $pdo = new PDO('mysql:host=主机地址;dbname=数据库名', '数据库用户名', '数据库密码');
    $sql = 'SELECT * FROM 表名 WHERE 字段1 = ? AND 字段2 = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$value1, $value2]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die('查询失败: ' . $e->getMessage());
}

Here, we use the "parameterized query" way to construct query statements. We pass the values ​​required in the query statement to the execute() method in the form of parameters instead of splicing multiple variables together as a query statement. This method can prevent SQL injection attacks and improve code security.

After executing the execute() method, the $result variable will save the array of query results. In addition, there are some other fetch methods that can be used to obtain query results row by row.

Inserting and updating data

It is also very easy to insert and update data into the database using PDO. When using PDO to insert data, we can use the execute() method and predefined parameter replacement characters to dynamically insert values, as shown in the following code:

try {
    $pdo = new PDO('mysql:host=主机地址;dbname=数据库名', '数据库用户名', '数据库密码');
    $sql = 'INSERT INTO 表名 (字段1, 字段2, 字段3) VALUES (?, ?, ?)';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$value1, $value2, $value3]);
    $result = $stmt->rowCount();
} catch (PDOException $e) {
    die('插入数据失败: ' . $e->getMessage());
}

When using PDO to update data, we can also use execute () method and predefined parameter replacement characters to dynamically update the specified field, as shown in the following code:

try {
    $pdo = new PDO('mysql:host=主机地址;dbname=数据库名', '数据库用户名', '数据库密码');
    $sql = 'UPDATE 表名 SET 字段1 = ? ,字段2 = ? WHERE 字段3 = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$value1, $value2, $value3]);
    $result = $stmt->rowCount();
} catch (PDOException $e) {
    die('更新数据失败: ' . $e->getMessage());
}

Here, we use the update statement to update the specified field. Similar to inserting data, PDO ensures the safety and correctness of the code by using dynamic predefined parameters.

Conclusion

In this article, we introduced the method of using PDO extension to connect and operate MySQL database. It focuses on how to use PDO parameterized queries to solve SQL injection problems, and demonstrates the process of inserting and updating data. To become an excellent web application developer, it is necessary to understand the core technology of PDO. It can improve developer productivity and greatly enhance the management and control of data by web applications.

The above is the detailed content of How to use PDO extension in php?. For more information, please follow other related articles on the PHP Chinese website!