PHP/MySQLi 优化标签显示：告别 N+1 查询

// 假设 $row["tags"] 的值为 "1,2,3"
$tags = json_decode(json_encode(explode(',', $row["tags"]))); // 示例中这步略显多余，explode已足够

foreach($tags as $tag) {
    $fetchTags = $conn->prepare("SELECT id, name FROM tags WHERE id = ? AND type = 1");
    $fetchTags->bind_param("i", $tag);
    $fetchTags->execute();
    $fetchResult = $fetchTags->get_result();
    if($fetchResult->num_rows === 0) {
        print('No rows');
    }
    while($resultrow = $fetchResult->fetch_assoc()) {
      ?><span class="badge bg-primary me-2"><?php echo $resultrow["name"]; ?></span><?php
    }
    $fetchTags->close();
}

<?php
// 假设 $conn 是已建立的 MySQLi 数据库连接
// 假设 $row["tags"] 的值为 "1,2,3"

// 1. 将逗号分隔的标签 ID 字符串转换为数组
$tags = explode(',', $row["tags"]);

// 确保 $tags 数组不为空，避免生成无效查询
if (empty($tags)) {
    // 没有标签，直接跳过
    return;
}

// 2. 动态生成 WHERE IN 子句的占位符
// 例如，如果 $tags 包含 3 个元素，则生成 "?,?,?"
$placeholders = implode(',', array_fill(0, count($tags), '?'));

// 3. 构建预处理语句
// 注意：ORDER BY id 可以确保结果的顺序一致，这在某些情况下可能有用
$fetchTags = $conn->prepare('SELECT id, name FROM tags WHERE id IN ('.$placeholders.') AND type = 1 ORDER BY id');

// 4. 动态绑定参数
// str_repeat('s', count($tags)) 生成与标签数量相匹配的类型字符串
// 例如，如果 $tags 包含 3 个元素，则生成 "sss"
// ...$tags (splat operator) 将数组元素作为单独的参数传递给 bind_param
$fetchTags->bind_param(str_repeat('s', count($tags)), ...$tags);

// 5. 执行查询
$fetchTags->execute();

// 6. 获取结果
$fetchResult = $fetchTags->get_result();

if($fetchResult->num_rows === 0) {
    print('No rows');
} else {
    // 遍历结果并显示标签
    foreach($fetchResult as $resultRow) {
        ?><span class="badge bg-primary me-2"><?php echo htmlspecialchars($resultRow["name"]); ?></span><?php
    }
}
// 关闭预处理语句
$fetchTags->close();
?>

<?php
// 假设 $conn 是已建立的 MySQLi 数据库连接
// 假设 $row["tags"] 的值为 "1,2,3"

$tags = explode(',', $row["tags"]);

if (empty($tags)) {
    return;
}

$placeholders = implode(',', array_fill(0, count($tags), '?'));
$fetchTags = $conn->prepare('SELECT id, name FROM tags WHERE id IN ('.$placeholders.') AND type = 1 ORDER BY id');

// PHP 8.1+ 简化绑定
$fetchTags->execute($tags); // 直接传递数组

$fetchResult = $fetchTags->get_result();

if($fetchResult->num_rows === 0) {
    print('No rows');
} else {
    foreach($fetchResult as $resultRow) {
        ?><span class="badge bg-primary me-2"><?php echo htmlspecialchars($resultRow["name"]); ?></span><?php
    }
}
$fetchTags->close();
?>