如何从SQL数据库JSON字段获取数量数据并嵌入现有WHILE循环？

我正在开发一个订单页面，供用户查看历史订单。当前获取数据的代码如下：

$qry = "SELECT * FROM `orders` WHERE `id` = ? AND `user_id` = ?";
$stmt = $conn->prepare($qry);
$stmt->bind_param("ii", $order, $id);
$stmt->execute();
$order = $stmt->get_result()->fetch_object();

$data = utf8_encode($order->products);
$dataAssoc = json_decode($data, true);

随后在HTML中读取并展示每个产品的标题、版本、价格等信息，代码如下：

if (!$dataAssoc == "") {
    foreach ($dataAssoc as $key => $value) {
        
        // 获取产品数据
        $qry = "SELECT * FROM `products` WHERE `id`= ?";
        $stmt = $conn->prepare($qry);
        $stmt->bind_param("i", $value);
        $stmt->execute();
        $productData = $stmt->get_result();
        
        while ($product = $productData->fetch_object()) {
            echo "
                <div class='col'>
                  <div class='product'>
                    <div class='thumbnail'>
                        <img src='assets/images/product/product-thumb/" . $product->thumbnail . "' alt='product image'>
                    </div>
                    <div class='product-content'>
                      <div class='inner'>
                        <h5 class='title'>" . $product->name . " (" . $product->version .")</h5>
                        <div class='product-price'>
                          <span class='price current-price'>€" . str_replace('.', ',', $product->price) . "</span>
                        </div>
                      </div>
                    </div>
                  </div>
                </div>
            ";
        }
    }
}

数据库中还有一个amounts字段，以JSON格式存储订购产品的数量（源自cart表），示例：{ "1":"3", "2":"2", "3":"7" }（第二个数字为数量）；产品字段的JSON格式类似，示例：{ "1":"9", "2":"12", "3":"2" }（第二个数字为产品ID）。

我尝试单独获取数量数据：

$amounts     = utf8_encode($order->amounts);
$amountAssoc = json_decode($amounts, true);

并将其插入原WHILE循环中，结果却是每次加载新产品时，所有产品数量都会连续输出。

请问最佳实现方式是什么？是调整现有WHILE循环结构、重构数据库，还是有我忽略的简单解决方案？

一、快速修复现有代码（简单解决方案） #

问题核心是没有正确关联产品ID和对应数量。$dataAssoc的键（如示例中的"1"、"2"）是订单内产品的索引，与$amountAssoc的键一一对应，$dataAssoc的值是产品ID。只需在循环中通过当前索引获取对应数量即可：

1. 确保数量数据正确解码：

$amounts     = utf8_encode($order->amounts);
$amountAssoc = json_decode($amounts, true);

2. 修改循环代码，加入数量输出并补充安全处理：

if (!empty($dataAssoc)) { // 用empty()判断数组是否为空，比字符串对比更准确
    foreach ($dataAssoc as $itemKey => $productId) {
        // 获取当前产品的订购数量，默认1避免无数据时出错
        $quantity = isset($amountAssoc[$itemKey]) ? $amountAssoc[$itemKey] : 1;
        
        // 获取产品数据
        $qry = "SELECT * FROM `products` WHERE `id`= ?";
        $stmt = $conn->prepare($qry);
        $stmt->bind_param("i", $productId);
        $stmt->execute();
        $productData = $stmt->get_result();
        
        while ($product = $productData->fetch_object()) {
            echo "
                <div class='col'>
                  <div class='product'>
                    <div class='thumbnail'>
                        <img src='assets/images/product/product-thumb/" . htmlspecialchars($product->thumbnail) . "' alt='product image'>
                    </div>
                    <div class='product-content'>
                      <div class='inner'>
                        <h5 class='title'>" . htmlspecialchars($product->name) . " (" . htmlspecialchars($product->version) .")</h5>
                        <div class='product-quantity'>
                          <span>数量: " . $quantity . "</span>
                        </div>
                        <div class='product-price'>
                          <span class='price current-price'>€" . str_replace('.', ',', htmlspecialchars($product->price)) . "</span>
                        </div>
                      </div>
                    </div>
                  </div>
                </div>
            ";
        }
    }
}

注：加入htmlspecialchars()是为了防止XSS攻击，输出数据库/用户内容时必须做安全转义。

二、优化查询性能（比快速修复更优） #

当前循环内单次查询产品属于N+1查询问题，订单产品越多性能越差。可以改成批量查询所有产品：

1. 批量获取所有产品数据：

$productIds = array_values($dataAssoc);
// 生成批量查询的占位符，如?, ?, ?
$placeholders = implode(',', array_fill(0, count($productIds), '?'));

// 批量查询产品
$qry = "SELECT * FROM `products` WHERE `id` IN ($placeholders)";
$stmt = $conn->prepare($qry);
// 绑定参数，类型字符串为对应数量的'i'
$stmt->bind_param(str_repeat('i', count($productIds)), ...$productIds);
$stmt->execute();
$productResult = $stmt->get_result();

// 将产品数据转为以ID为键的数组，方便快速查找
$productsById = [];
while ($product = $productResult->fetch_object()) {
    $productsById[$product->id] = $product;
}

2. 循环输出时直接从数组取数据：

if (!empty($dataAssoc)) {
    foreach ($dataAssoc as $itemKey => $productId) {
        $quantity = isset($amountAssoc[$itemKey]) ? $amountAssoc[$itemKey] : 1;
        if (!isset($productsById[$productId])) continue; // 跳过不存在的产品
        $product = $productsById[$productId];
        
        echo "
            <div class='col'>
              <div class='product'>
                <div class='thumbnail'>
                    <img src='assets/images/product/product-thumb/" . htmlspecialchars($product->thumbnail) . "' alt='product image'>
                </div>
                <div class='product-content'>
                  <div class='inner'>
                    <h5 class='title'>" . htmlspecialchars($product->name) . " (" . htmlspecialchars($product->version) .")</h5>
                    <div class='product-quantity'>
                      <span>数量: " . $quantity . "</span>
                    </div>
                    <div class='product-price'>
                      <span class='price current-price'>€" . str_replace('.', ',', htmlspecialchars($product->price)) . "</span>
                    </div>
                  </div>
                </div>
              </div>
            </div>
        ";
    }
}

这种方式只需要1次产品查询，性能提升明显。

三、数据库重构（长期最优方案） #

用JSON存储订单产品和数量虽简单，但不利于数据维护、查询和扩展（如统计销量、关联订单明细）。推荐重构为规范化的关联表结构：

1. 创建order_items订单明细表：

CREATE TABLE `order_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `quantity` INT NOT NULL DEFAULT 1,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT
);

2. 移除原orders表中的products和amounts字段。
3. 查询时直接关联获取订单明细：

$qry = "
    SELECT p.*, oi.quantity 
    FROM `orders` o
    JOIN `order_items` oi ON o.id = oi.order_id
    JOIN `products` p ON oi.product_id = p.id
    WHERE o.id = ? AND o.user_id = ?
";
$stmt = $conn->prepare($qry);
$stmt->bind_param("ii", $orderId, $userId);
$stmt->execute();
$result = $stmt->get_result();

while ($item = $result->fetch_object()) {
    echo "
        <div class='col'>
          <div class='product'>
            <div class='thumbnail'>
                <img src='assets/images/product/product-thumb/" . htmlspecialchars($item->thumbnail) . "' alt='product image'>
            </div>
            <div class='product-content'>
              <div class='inner'>
                <h5 class='title'>" . htmlspecialchars($item->name) . " (" . htmlspecialchars($item->version) .")</h5>
                <div class='product-quantity'>
                  <span>数量: " . $item->quantity . "</span>
                </div>
                <div class='product-price'>
                  <span class='price current-price'>€" . str_replace('.', ',', htmlspecialchars($item->price)) . "</span>
                </div>
              </div>
            </div>
          </div>
        </div>
    ";
}

该结构的优势：

• 避免JSON解析的复杂度和潜在错误
• 支持更灵活的查询（如统计单个产品的总销量）
• 符合数据库设计规范化原则
• 数据修改和维护更便捷

内容的提问来源于stack exchange，提问作者Cazlo