当前位置：首页 > PHP

php实现数据库查询

2026-02-13 18:22:13PHP

使用PDO连接数据库

PDO（PHP Data Objects）是PHP中连接数据库的扩展，支持多种数据库类型。以下代码展示如何连接MySQL数据库：

$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch (PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}

执行简单查询

通过PDO的query()方法执行SELECT查询，获取结果集：

$sql = "SELECT id, name, email FROM users";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "<br>";
}

使用预处理语句防止SQL注入

预处理语句通过prepare()和execute()实现，适用于带参数的查询：

$user_id = 1;
$sql = "SELECT name FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

if ($result) {
    echo "用户名: " . $result['name'];
} else {
    echo "未找到用户";
}

插入数据示例

通过预处理语句插入数据，并获取最后插入的ID：

$name = "John Doe";
$email = "john@example.com";

$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute([':name' => $name, ':email' => $email]);

$last_id = $pdo->lastInsertId();
echo "新记录ID: " . $last_id;

更新和删除数据

更新数据示例：

$new_email = "new_email@example.com";
$user_id = 1;

$sql = "UPDATE users SET email = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$new_email, $user_id]);
echo "更新行数: " . $stmt->rowCount();

删除数据示例：

$user_id = 1;
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
echo "删除行数: " . $stmt->rowCount();

错误处理与事务

使用事务确保数据一致性：

php实现数据库查询

try {
    $pdo->beginTransaction();

    $sql1 = "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1";
    $pdo->exec($sql1);

    $sql2 = "UPDATE accounts SET balance = balance + 100 WHERE user_id = 2";
    $pdo->exec($sql2);

    $pdo->commit();
    echo "事务成功";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "事务失败: " . $e->getMessage();
}