当前位置：首页 > PHP

php实现数据库查询

2026-01-28 20:11:14PHP

使用PDO连接数据库

确保PHP已安装PDO扩展，通过以下代码建立与MySQL数据库的连接：

$host = 'localhost';
$dbname = 'database_name';
$username = 'username';
$password = 'password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

执行简单查询

使用query()方法执行SELECT语句并获取结果：

$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . "<br>";
}

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

通过prepare()和execute()处理带参数的查询，提升安全性：

$sql = "SELECT * FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

插入数据示例

通过预处理语句插入新记录：

$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => 'test',
    'email' => 'test@example.com'
]);
echo "Inserted ID: " . $pdo->lastInsertId();

更新和删除操作

更新记录：

$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => 'new@example.com', 'id' => 1]);

删除记录：

$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1]);

错误处理

捕获并处理数据库操作中的异常：

try {
    $stmt = $pdo->prepare("INVALID SQL");
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

事务处理

确保多条SQL语句的原子性：

$pdo->beginTransaction();
try {
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

php实现数据库查询