php实现数据查询

数据库连接

使用PDO或MySQLi扩展连接数据库。PDO支持多种数据库，MySQLi专用于MySQL。

PDO连接示例

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

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

MySQLi连接示例

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

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

基本查询

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

PDO查询示例

$stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

MySQLi查询示例

$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

结果处理

遍历查询结果并输出数据。

PDO结果处理

foreach ($result as $row) {
    echo "ID: " . $row['id'] . ", Name: " . $row['name'] . "<br>";
}

MySQLi结果处理

foreach ($result as $row) {
    echo "ID: " . $row['id'] . ", Name: " . $row['name'] . "<br>";
}

条件查询

根据条件动态构建查询语句。

$name = 'John';
$age = 25;

$sql = "SELECT * FROM users WHERE name = ? AND age > ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("si", $name, $age);
$stmt->execute();
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

排序和分页

添加排序和分页功能。

$page = 1;
$limit = 10;
$offset = ($page - 1) * $limit;

$sql = "SELECT * FROM users ORDER BY name ASC LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $limit, $offset);
$stmt->execute();
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

错误处理

捕获并处理查询过程中的错误。

PDO错误处理

try {
    $stmt = $conn->prepare("SELECT * FROM non_existent_table");
    $stmt->execute();
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

MySQLi错误处理

$result = $conn->query("SELECT * FROM non_existent_table");
if (!$result) {
    echo "Error: " . $conn->error;
}

关闭连接

查询完成后关闭数据库连接。

PDO关闭连接

$conn = null;

MySQLi关闭连接

$conn->close();