您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
# 如何用PHP从MySQL数据库中读出查询的数据
## 目录
1. [前言](#前言)
2. [环境准备](#环境准备)
3. [连接MySQL数据库](#连接mysql数据库)
4. [执行基础查询](#执行基础查询)
5. [处理查询结果](#处理查询结果)
6. [预处理语句](#预处理语句)
7. [分页查询实现](#分页查询实现)
8. [高级查询技巧](#高级查询技巧)
9. [错误处理与调试](#错误处理与调试)
10. [性能优化](#性能优化)
11. [安全注意事项](#安全注意事项)
12. [完整示例](#完整示例)
13. [总结](#总结)
## 前言
PHP与MySQL的组合是Web开发中最经典的技术栈之一。根据W3Techs的统计，截至2023年，约有77%的使用服务器端编程语言的网站采用PHP，而MySQL在开源关系型数据库中的市场份额超过40%。本文将全面讲解如何使用PHP从MySQL数据库中读取数据，涵盖从基础到高级的各种技术细节。
## 环境准备
### 1.1 开发环境要求
- PHP 7.4+ (推荐8.0+)
- MySQL 5.7+ (推荐8.0+)
- Web服务器(Apache/Nginx)
- phpMyAdmin(可选)
### 1.2 安装必要的扩展
确保已安装并启用以下PHP扩展：
```ini
extension=mysqli
extension=pdo_mysql
CREATE DATABASE php_mysql_demo;
USE php_mysql_demo;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('bob_johnson', 'bob@example.com');
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "php_mysql_demo";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>
<?php
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// 设置PDO错误模式为异常
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "连接成功";
} catch(PDOException $e) {
echo "连接失败: " . $e->getMessage();
}
?>
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 结果";
}
$stmt = $conn->query($sql);
if ($stmt->rowCount() > 0) {
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 结果";
}
| 查询类型 | 返回值 | 适用场景 |
|---|---|---|
| SELECT | 结果集对象 | 数据检索 |
| INSERT | 布尔值/插入ID | 数据插入 |
| UPDATE/DELETE | 受影响的行数 | 数据修改/删除 |
MySQLi获取方式:
$row = $result->fetch_assoc(); // 关联数组
$row = $result->fetch_row(); // 索引数组
$row = $result->fetch_array(); // 两者都有
$row = $result->fetch_object(); // 对象
PDO获取方式:
$row = $stmt->fetch(PDO::FETCH_ASSOC); // 关联数组
$row = $stmt->fetch(PDO::FETCH_NUM); // 索引数组
$row = $stmt->fetch(PDO::FETCH_BOTH); // 两者都有
$row = $stmt->fetch(PDO::FETCH_OBJ); // 对象
一次性获取所有数据:
// MySQLi
$all_rows = $result->fetch_all(MYSQLI_ASSOC);
// PDO
$all_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
获取特定列:
$usernames = array_column($all_rows, 'username');
// 获取字段数
$field_count = $result->field_count;
// 获取字段信息
$fields = $result->fetch_fields();
foreach ($fields as $field) {
echo "字段名: $field->name, 类型: $field->type, 最大长度: $field->max_length<br>";
}
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$user_id = 1;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// 处理数据
}
$stmt = $conn->prepare("SELECT * FROM users WHERE email LIKE :email");
$email = "%example%";
$stmt->bindParam(':email', $email);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// 处理数据
}
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$users = [
['alice', 'alice@example.com'],
['bob', 'bob@example.com']
];
foreach ($users as $user) {
$username = $user[0];
$email = $user[1];
$stmt->execute();
}
$per_page = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;
$sql = "SELECT * FROM users LIMIT $start, $per_page";
$result = $conn->query($sql);
// 获取总记录数
$total_result = $conn->query("SELECT COUNT(*) as total FROM users");
$total_row = $total_result->fetch_assoc();
$total_pages = ceil($total_row['total'] / $per_page);
// 使用带WHERE条件的COUNT
$count_sql = "SELECT COUNT(*) as total FROM users WHERE active = 1";
// 使用JOIN时分页优化
$sql = "SELECT u.* FROM users u
JOIN user_profiles up ON u.id = up.user_id
WHERE up.country = 'US'
LIMIT $start, $per_page";
游标分页(Keyset Pagination):
$last_id = isset($_GET['last_id']) ? (int)$_GET['last_id'] : 0;
$sql = "SELECT * FROM users WHERE id > $last_id ORDER BY id ASC LIMIT $per_page";
$conditions = [];
$params = [];
$types = '';
if (!empty($_GET['username'])) {
$conditions[] = "username LIKE ?";
$params[] = '%' . $_GET['username'] . '%';
$types .= 's';
}
if (!empty($_GET['email'])) {
$conditions[] = "email = ?";
$params[] = $_GET['email'];
$types .= 's';
}
$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
$sql = "SELECT * FROM users $where";
$stmt = $conn->prepare($sql);
if ($types) {
$stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
$sql = "SELECT u.username, u.email, p.bio, p.avatar
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.active = 1";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
// 处理关联数据
}
$sql = "SELECT u.*,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u
HAVING order_count > 0";
try {
$conn->begin_transaction();
// 执行多个查询
$conn->query("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$conn->query("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
echo "事务失败: " . $e->getMessage();
}
// MySQLi错误处理
if (!$result = $conn->query($sql)) {
error_log("MySQL错误: " . $conn->error);
// 显示用户友好信息
die("查询时出现问题，请稍后再试");
}
// PDO错误处理
$stmt = $conn->prepare($sql);
if (!$stmt->execute()) {
$error = $stmt->errorInfo();
error_log("PDO错误: " . $error[2]);
}
// 获取实际执行的SQL(PDO)
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$debug_sql = $stmt->debugDumpParams();
// 记录慢查询
$start = microtime(true);
// 执行查询
$time_elapsed = microtime(true) - $start;
if ($time_elapsed > 0.5) { // 超过500ms
error_log("慢查询: $sql ($time_elapsed 秒)");
}
// 使用APCu缓存查询结果
$cache_key = 'users_list_' . md5($sql);
if (apcu_exists($cache_key)) {
$users = apcu_fetch($cache_key);
} else {
$result = $conn->query($sql);
$users = $result->fetch_all(MYSQLI_ASSOC);
apcu_store($cache_key, $users, 3600); // 缓存1小时
}
对于高并发应用，考虑使用:
- MySQLi持久连接(p:前缀)
- PDO连接池
- 第三方连接池解决方案
// 输出前转义HTML
echo htmlspecialchars($row['username'], ENT_QUOTES, 'UTF-8');
// JSON输出
header('Content-Type: application/json');
echo json_encode($data, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT);
<?php
require 'config/database.php';
class UserManager {
private $conn;
public function __construct($conn) {
$this->conn = $conn;
}
public function getUsers($page = 1, $per_page = 10, $search = '') {
$start = ($page - 1) * $per_page;
$params = [];
$where = '';
if (!empty($search)) {
$where = "WHERE username LIKE ? OR email LIKE ?";
$search_term = "%$search%";
$params = [$search_term, $search_term];
}
$sql = "SELECT SQL_CALC_FOUND_ROWS id, username, email, created_at
FROM users $where
ORDER BY created_at DESC
LIMIT ?, ?";
$stmt = $this->conn->prepare($sql);
$types = str_repeat('s', count($params)) . 'ii';
$params = array_merge($params, [$start, $per_page]);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$users = $result->fetch_all(MYSQLI_ASSOC);
// 获取总记录数
$total_result = $this->conn->query("SELECT FOUND_ROWS() as total");
$total = $total_result->fetch_assoc()['total'];
return [
'users' => $users,
'total' => $total,
'pages' => ceil($total / $per_page)
];
}
public function getUserById($id) {
$stmt = $this->conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
return $result->fetch_assoc();
}
}
// 使用示例
$userManager = new UserManager($conn);
$data = $userManager->getUsers($_GET['page'] ?? 1, 10, $_GET['search'] ?? '');
header('Content-Type: application/json');
echo json_encode($data);
?>
本文全面介绍了使用PHP从MySQL数据库读取数据的各种技术和方法，包括：
通过掌握这些技术，您可以构建安全、高效的数据驱动型PHP应用程序。随着PHP和MySQL的持续发展，建议关注以下趋势：
希望本文能成为您在PHP和MySQL开发中的实用参考指南。
| 函数名 | 描述 |
|---|---|
| mysqli_connect() | 建立数据库连接 |
| mysqli_query() | 执行SQL查询 |
| mysqli_fetch_assoc() | 获取关联数组形式的结果行 |
| mysqli_prepare() | 准备预处理语句 |
| mysqli_stmt_execute() | 执行预处理语句 |
| 方法名 | 描述 |
|---|---|
| query() | 执行SQL语句并返回结果集 |
| exec() | 执行SQL并返回受影响的行数 |
| prepare() | 准备预处理语句 |
| execute() | 执行预处理语句 |
| fetch() | 获取下一行 |
| fetchAll() | 获取所有结果行 |
”`
注：本文实际字数为约4500字，要达到7300字需要进一步扩展每个章节的细节，添加更多实际案例、性能对比数据、错误处理场景示例等。您需要我继续扩展哪些部分的内容？
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。