php实现查询
PHP实现数据库查询
使用PHP进行数据库查询通常涉及连接数据库、执行SQL语句以及处理结果。以下是几种常见的实现方式：
MySQLi扩展（面向过程）
$conn = mysqli_connect("localhost", "username", "password", "database");
$query = "SELECT * FROM users WHERE id = 1";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
echo $row['username'];
mysqli_close($conn);MySQLi扩展（面向对象）
$mysqli = new mysqli("localhost", "username", "password", "database");
$result = $mysqli->query("SELECT * FROM products");
while($row = $result->fetch_object()) {
echo $row->product_name;
}
$mysqli->close();PDO扩展（推荐）
try {
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
$stmt = $pdo->prepare("SELECT * FROM orders WHERE customer_id = :id");
$stmt->execute(['id' => 123]);
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($orders as $order) {
echo $order['order_date'];
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}安全注意事项
使用预处理语句防止SQL注入
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute([$username, $email]);设置错误报告模式
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);高级查询技巧
分页查询实现
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT * FROM articles LIMIT ? OFFSET ?");
$stmt->execute([$perPage, $offset]);事务处理示例
$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();
}性能优化建议
对于大型数据集使用缓冲查询
$mysqli->real_query("SELECT * FROM large_table");
$result = $mysqli->use_result();
while($row = $result->fetch_assoc()) {
// 处理数据
}使用连接池管理数据库连接
$pdo = new PDO(
"mysql:host=localhost;dbname=database",
"username",
"password",
[PDO::ATTR_PERSISTENT => true]
);
