PHP MySQL 读取数据

读取数据库中的数据是PHP MySQL操作中最常见的任务之一。本教程将介绍如何使用PHP从MySQL数据库中读取数据，包括MySQLi扩展和PDO（PHP数据对象）两种方式，以及不同的结果获取方法。

使用MySQLi读取数据

MySQLi扩展提供了面向对象和过程式两种方式来读取数据。

1. MySQLi面向对象方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// SQL查询语句
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

// 检查结果集
if ($result->num_rows > 0) {
    // 输出每行数据
    while (row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - 名: " . $row["firstname"] . " - 姓: " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 结果";
}

// 关闭连接
$conn->close();
?>

2. MySQLi过程式方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// SQL查询语句
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

// 检查结果集
if (mysqli_num_rows($result) > 0) {
    // 输出每行数据
    while (row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - 名: " . $row["firstname"] . " - 姓: " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 结果";
}

// 关闭连接
mysqli_close($conn);
?>

MySQLi的不同结果获取方式

MySQLi提供了多种获取结果的方法：

fetch_assoc() - 以关联数组形式获取结果
fetch_row() - 以索引数组形式获取结果
fetch_array() - 以关联数组和索引数组两种形式获取结果
fetch_object() - 以对象形式获取结果

<?php
// 数据库连接代码...

// 1. fetch_assoc() - 关联数组
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while (row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . ", 名: " . $row["firstname"] . "<br>";
    }
}

// 2. fetch_row() - 索引数组
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while (row = $result->fetch_row()) {
        echo "ID: " . $row[0] . ", 名: " . $row[1] . "<br>";
    }
}

// 3. fetch_array() - 关联数组和索引数组
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while (row = $result->fetch_array()) {
        echo "ID: " . $row[0] . " 或 " . $row["id"] . ", 名: " . $row[1] . " 或 " . $row["firstname"] . "<br>";
    }
}

// 4. fetch_object() - 对象
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while (row = $result->fetch_object()) {
        echo "ID: " . $row->id . ", 名: " . $row->firstname . "<br>";
    }
}
?>

使用MySQLi预处理语句读取数据

使用预处理语句读取数据是一种更安全的方式，特别是当查询条件包含用户输入时。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 准备预处理语句
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = ?");
$stmt->bind_param("s", $lastname);

// 设置参数
$lastname = "Doe";

// 执行查询
$stmt->execute();

// 绑定结果变量
$stmt->bind_result($id, $firstname, $lastname);

// 获取结果
while ($stmt->fetch()) {
    echo "ID: " . $id . " - 名: " . $firstname . " - 姓: " . $lastname . "<br>";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

使用PDO读取数据

PDO（PHP数据对象）提供了一种与数据库类型无关的方式来读取数据。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // SQL查询语句
    $sql = "SELECT id, firstname, lastname FROM MyGuests";
    
    // 准备和执行查询
    $stmt = $conn->query($sql);
    
    // 设置获取模式为关联数组
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    // 获取所有结果
    $results = $stmt->fetchAll();
    
    // 输出结果
    if (count($results) > 0) {
        foreach ($results as row) {
            echo "ID: " . $row["id"] . " - 名: " . $row["firstname"] . " - 姓: " . $row["lastname"] . "<br>";
        }
    } else {
        echo "0 结果";
    }
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

PDO的不同获取模式

PDO提供了多种获取结果的模式：

PDO::FETCH_ASSOC - 以关联数组形式获取结果
PDO::FETCH_NUM - 以索引数组形式获取结果
PDO::FETCH_BOTH - 以关联数组和索引数组两种形式获取结果（默认）
PDO::FETCH_OBJ - 以对象形式获取结果
PDO::FETCH_CLASS - 以指定类的实例形式获取结果
PDO::FETCH_COLUMN - 只获取指定的列

<?php
// 数据库连接代码...

// SQL查询语句
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$stmt = $conn->query($sql);

// 1. PDO::FETCH_ASSOC - 关联数组
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while (row = $stmt->fetch()) {
    echo "ID: " . $row["id"] . ", 名: " . $row["firstname"] . "<br>";
}

// 重新执行查询
$stmt = $conn->query($sql);

// 2. PDO::FETCH_NUM - 索引数组
$stmt->setFetchMode(PDO::FETCH_NUM);
while (row = $stmt->fetch()) {
    echo "ID: " . $row[0] . ", 名: " . $row[1] . "<br>";
}

// 重新执行查询
$stmt = $conn->query($sql);

// 3. PDO::FETCH_OBJ - 对象
$stmt->setFetchMode(PDO::FETCH_OBJ);
while (row = $stmt->fetch()) {
    echo "ID: " . $row->id . ", 名: " . $row->firstname . "<br>";
}

// 4. PDO::FETCH_CLASS - 类实例
class Guest {
    public $id;
    public $firstname;
    public $lastname;
}

$stmt = $conn->query($sql);
$guests = $stmt->fetchAll(PDO::FETCH_CLASS, 'Guest');

foreach ($guests as guest) {
    echo "ID: " . $guest->id . ", 名: " . $guest->firstname . "<br>";
}

// 5. PDO::FETCH_COLUMN - 单列
$stmt = $conn->query($sql);
$firstnames = $stmt->fetchAll(PDO::FETCH_COLUMN, 1); // 1 是firstname列的索引

foreach ($firstnames as firstname) {
    echo "名: " . $firstname . "<br>";
}
?>

使用PDO预处理语句读取数据

使用PDO预处理语句读取数据可以安全地处理用户输入。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 准备预处理语句
    $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = :lastname");
    
    // 执行查询
    $stmt->execute([
        ':lastname' => "Doe"
    ]);
    
    // 设置获取模式
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    // 获取结果
    while (row = $stmt->fetch()) {
        echo "ID: " . $row['id'] . " - 名: " . $row['firstname'] . " - 姓: " . $row['lastname'] . "<br>";
    }
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

读取数据并显示在HTML表格中

在实际应用中，我们经常需要将查询结果以表格形式展示给用户。

<?php
// 数据库连接代码...

// SQL查询语句
$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";
$result = $conn->query($sql);
?>

<!DOCTYPE html>
<html>
<head>
<style>
/* 简单的表格样式 */
table {
    width: 100%;
    border-collapse: collapse;
}
table, th, td {
    border: 1px solid black;
}
th, td {
    padding: 10px;
    text-align: left;
}
th {
    background-color: #f2f2f2;
}
tr:nth-child(even) {
    background-color: #f9f9f9;
}
</style>
</head>
<body>

<h2>用户列表</h2>

<table>
    <tr>
        <th>ID</th>
        <th>名</th>
        <th>姓</th>
        <th>邮箱</th>
        <th>注册日期</th>
    </tr>
    <?php
    if ($result->num_rows > 0) {
        // 输出每行数据
        while (row = $result->fetch_assoc()) {
            echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] . "</td><td>" . $row["lastname"] . "</td><td>" . $row["email"] . "</td><td>" . $row["reg_date"] . "</td></tr>";
        }
    } else {
        echo "<tr><td colspan='5'>0 结果</td></tr>";
    }
    $conn->close();
    >
</table>

</body>
</html>

分页读取数据

当数据量很大时，通常需要实现分页功能。

<?php
// 数据库连接代码...

// 配置分页参数
$records_per_page = 10; // 每页显示的记录数

// 获取当前页码，如果没有设置则默认为1
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$page = $page < 1 ? 1 : $page; // 确保页码不小于1

// 计算偏移量
$offset = ($page - 1) * $records_per_page;

// 1. 获取总记录数
$total_sql = "SELECT COUNT(*) as total FROM MyGuests";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_records = $total_row['total'];

// 计算总页数
$total_pages = ceil($total_records / $records_per_page);

// 2. 获取当前页的数据
$sql = "SELECT id, firstname, lastname, email FROM MyGuests LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $records_per_page, $offset);
$stmt->execute();
$result = $stmt->get_result();

// 显示数据
if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>名</th><th>姓</th><th>邮箱</th></tr>";
    
    while (row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] . "</td><td>" . $row["lastname"] . "</td><td>" . $row["email"] . "</td></tr>";
    }
    
    echo "</table>";
    
    // 显示分页链接
    echo "<div class='pagination'>";
    
    // 上一页链接
    if ($page > 1) {
        echo "<a href='?page=" . ($page - 1) . "'>上一页</a>";
    }
    
    // 页码链接
    for (i = 1; i <= $total_pages; i++) {
        if (i == $page) {
            echo "<a href='?page=" . i . "' class='active'>" . i . "</a>";
        } else {
            echo "<a href='?page=" . i . "'>" . i . "</a>";
        }
    }
    
    // 下一页链接
    if ($page < $total_pages) {
        echo "<a href='?page=" . ($page + 1) . "'>下一页</a>";
    }
    
    echo "</div>";
} else {
    echo "0 结果";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

统计和聚合查询

MySQL提供了多种聚合函数来统计和计算数据。

<?php
// 数据库连接代码...

// 1. 计算记录总数
$sql = "SELECT COUNT(*) as total FROM MyGuests";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo "总记录数: " . $row['total'] . "<br>";

// 2. 计算平均值
$sql = "SELECT AVG(price) as avg_price FROM products";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo "平均价格: " . $row['avg_price'] . "<br>";

// 3. 计算最大值和最小值
$sql = "SELECT MAX(price) as max_price, MIN(price) as min_price FROM products";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo "最高价格: " . $row['max_price'] . ", 最低价格: " . $row['min_price'] . "<br>";

// 4. 计算总和
$sql = "SELECT SUM(quantity) as total_quantity FROM order_items";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo "总数量: " . $row['total_quantity'] . "<br>";

// 5. 分组统计
$sql = "SELECT category, COUNT(*) as count FROM products GROUP BY category";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>按分类统计</h3>";
    while (row = $result->fetch_assoc()) {
        echo "分类: " . $row['category'] . ", 数量: " . $row['count'] . "<br>";
    }
}

// 关闭连接
$conn->close();
?>

读取大型结果集

当处理大型结果集时，我们需要考虑内存使用问题。

<?php
// 数据库连接代码...

// 使用MySQLi的unbuffered查询（减少内存使用）
$sql = "SELECT id, data FROM large_table";
$result = $conn->query($sql, MYSQLI_USE_RESULT);

if ($result) {
    while (row = $result->fetch_assoc()) {
        // 处理每一行数据，但不要将所有数据存储在内存中
        process_row($row);
    }
    $result->close();
}

// 使用PDO的游标（cursor）处理大型结果集
$stmt = $conn->prepare("SELECT id, data FROM large_table", [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$stmt->execute();

while (row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
    // 处理每一行数据
    process_row($row);
}
?>

最佳实践

只选择需要的列：不要使用 SELECT *，而是明确指定需要的列，以提高性能
使用预处理语句：当查询包含用户输入时，使用预处理语句防止SQL注入
限制结果集大小：使用LIMIT子句限制返回的记录数，特别是在开发和测试阶段
处理空结果集：始终检查查询是否返回了数据
使用索引：确保查询中使用的列有适当的索引，以提高查询性能
关闭结果集和连接：操作完成后，及时关闭结果集和数据库连接
优化大型结果集：对于大型结果集，考虑使用分页或流式读取
处理错误：使用try-catch块或错误处理函数捕获和处理可能出现的错误
使用缓存：对于不经常变化的数据，考虑使用缓存机制减少数据库查询
监控性能：使用EXPLAIN等工具分析查询性能，找出优化空间