php实现数据查询

PHP 实现数据查询的方法

使用 MySQLi 扩展

连接到 MySQL 数据库并执行查询：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

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

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

// 执行查询
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

// 处理结果
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

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

使用 PDO 扩展

使用 PDO 进行更安全的数据库操作：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("SELECT id, name, email FROM users"); 
    $stmt->execute();

    // 设置结果集为关联数组
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

    foreach($stmt->fetchAll() as $row) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
    }
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}
$conn = null;
?>

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

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);

$email = "user@example.com";
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // 处理数据
}
?>

使用 PDO 预处理语句

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE email = :email");
$stmt->bindParam(':email', $email);

$email = "user@example.com";
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    // 处理数据
}
?>

使用 LIMIT 分页查询

<?php
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 10;
$start = ($page > 1) ? ($page * $perPage) - $perPage : 0;

$sql = "SELECT * FROM posts LIMIT {$start}, {$perPage}";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    // 显示数据
}
?>

使用 JOIN 查询关联表

<?php
$sql = "SELECT users.name, orders.product, orders.amount 
        FROM users 
        INNER JOIN orders ON users.id = orders.user_id";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    echo $row["name"]." 购买了 ".$row["product"]." 数量 ".$row["amount"]."<br>";
}
?>

使用 WHERE 条件查询

<?php
$sql = "SELECT * FROM products WHERE price > 50 AND stock > 0";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    // 显示产品数据
}
?>

使用 ORDER BY 排序

<?php
$sql = "SELECT * FROM products ORDER BY price DESC";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    // 显示按价格降序排列的产品
}
?>

使用 GROUP BY 分组

<?php
$sql = "SELECT category, COUNT(*) as count FROM products GROUP BY category";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    echo "分类 ".$row["category"]." 有 ".$row["count"]." 个产品<br>";
}
?>

使用 LIKE 模糊查询

<?php
$search = "%apple%";
$sql = "SELECT * FROM products WHERE name LIKE ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $search);
$stmt->execute();

$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
    // 显示匹配的产品
}
?>