How to use php extension PDO to connect to MySQL database

How to use PHP to extend PDO to connect to MySQL database

1. What is PDO?
PDO (PHP Data Objects) is an interface for operating databases in PHP. It can support a variety of databases. It also provides consistent methods and interfaces to facilitate developers to perform database operations. PDO has higher performance, better portability and better security than traditional MySQL extensions.

2. Install the PDO extension
Before using PDO for MySQL database operations, you first need to install the PDO extension. Generally, PHP has the PDO extension installed by default. If it is not installed, you can enable the PDO extension in the php.ini configuration file, find the following two lines of code and delete the preceding comment symbol (semicolon):

;extension=pdo_mysql
;extension=pdo_sqlite

After deleting the comment symbol, restart the web server to take effect .

3. Connect to MySQL database
Before connecting to the MySQL database, you need to create a PDO object and configure the database connection parameters. The following is a sample code to connect to a local MySQL database:

<?php
$host = 'localhost';  // MySQL主机
$dbname = 'testdb';  // 数据库名
$username = 'root';  // 用户名
$password = 'password';  // 密码

try {
    $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8";  // 数据源名称
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,  // 设置错误处理模式为异常模式
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,  // 设置默认的结果集获取模式为关联数组模式
    ];

    $pdo = new PDO($dsn, $username, $password, $options);  // 创建PDO对象
    echo "连接成功！";
} catch (PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}
?>

In the above code, we use a try-catch block to catch possible exceptions. The parameters of the PDO constructor are the database source name (composed of database type, host and database name), user name, password and optional configuration options.

4. Execute SQL statements
After the connection is successful, you can execute the SQL statement. PDO provides two methods, exec() and query(), to execute SQL statements. The exec() method is used to execute statements that return no results (such as INSERT, UPDATE, DELETE), and the query() method is used to execute statements that return a result set (such as SELECT).

The following is a sample code for inserting data:

<?php
$sql = "INSERT INTO users (username, email) VALUES ('johndoe', 'johndoe@example.com')";
$affectedRows = $pdo->exec($sql);
echo "插入成功，影响行数为：" . $affectedRows;
?>

The following is a sample code for querying data:

<?php
$sql = "SELECT * FROM users";
$result = $pdo->query($sql);
while ($row = $result->fetch()) {
    echo "用户名：" . $row['username'] . "，邮箱：" . $row['email'] . "<br>";
}
?>

It should be noted that after executing the query statement, you need to use The fetch() method is used to obtain the data in the result set row by row.

5. Close the database connection
After completing the database operation, the database connection should be closed in time to release resources. You can use the null attribute of the PDO object to close the connection:

<?php
$pdo = null;
echo "数据库连接已关闭！";
?>

6. Summary
It is very simple to use PHP to extend PDO to connect to the MySQL database. You only need to create a PDO object and configure the connection parameters to perform database operations. . Through PDO, operations such as addition, deletion, modification, and query of the MySQL database can be realized, and security issues such as SQL injection can also be solved.

The above is how to use PHP to extend PDO to connect to the MySQL database. I hope it will be helpful to you!

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