PHP PDO 连接数据库

PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. It provides a data-access abstraction layer which means it can work with various database systems. This tutorial will guide you through the process of using PDO to connect and interact with a database in PHP.

概述

This tutorial assumes that you have a basic understanding of PHP and a local development environment set up. We'll be connecting to a MySQL database, but similar steps can be taken for other databases like PostgreSQL or SQLite.

PDO vs mysqli

Before we dive into PDO, it's important to understand the differences between PDO and another popular extension in PHP - mysqli. Both are used for connecting to a database and executing queries, but they have some notable differences:

  • mysqli: This is an object-oriented interface that primarily works with MySQL databases. It's also procedural.

  • PDO: This is a database access layer provided by PHP which stands for PHP Data Objects. It provides a data-access abstraction layer, meaning it can work with multiple databases (MySQL, PostgreSQL, SQLite, Oracle, etc.). PDO uses the driver model to connect to the database server.

安装和启用PDO

Most PHP installations will have PDO enabled by default. However, if it's not enabled in your environment, you can enable it by uncommenting the following line in your php.ini file:

extension=pdo_mysql

You may need to restart your web server for changes to take effect.

连接数据库

Here's a basic example of how to connect to a database using PDO:

<?php
$host = 'localhost';
$db   = 'test_db';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
?>

In this example:

  • $host is the hostname of your database server.
  • $db is the name of your database.
  • $user and $pass are your database username and password.
  • $charset is the character set that you want to use for the connection.
  • $dsn is a Data Source Name, which contains information about the server's type, hostname, and other options.
  • $opt is an array of options used to set attributes for the database connection. In this case, we're setting the error mode to throw exceptions on error, the default fetch mode to fetch as an associative array, and emulating prepared statements to false.
  • Finally, we create a new PDO instance with the $dsn, $user, $pass, and $opt variables.

执行查询

Now that you have a database connection, you can execute queries using the query() method or prepared statements for security reasons:

<?php
// Simple query execution
$sql = "SELECT * FROM users WHERE id = 1";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch()) {
    echo $row['name'];
}

// Prepared statement for security
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['John Doe', 'john@example.com']);
?>

In the first example, we're executing a simple query to select all users with an ID of 1 and then fetching each row from the result set.

In the second example, we're using a prepared statement to insert a new user into the database. This is more secure than directly concatenating variables into your SQL queries because it helps prevent SQL injection attacks.

错误处理

When an error occurs during a database operation, PDO can throw an exception. To catch these exceptions and handle them gracefully, you can use try-catch blocks:

<?php
try {
    $sql = "SELECT * FROM nonexistent_table";
    $pdo->query($sql);
} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

In this example, if an error occurs when trying to select from a non-existent table, the script will throw a PDOException. We're catching this exception and then printing out the error message before terminating the script.