PHP+MySQL嵌套循环生成JSON触发内存耗尽错误求助

Hey there, let's break down why you're hitting that "Allowed memory size exhausted" error and fix it step by step. The main culprit here is a simple oversight in your loop logic, plus some inefficient database queries that are compounding the problem.

1. The Immediate Fix: Reset Your Seller Array #

Right now, you're declaring $seller_array once at the top of your script. Every time you process a new article, you keep pushing seller data into the same array—this means every subsequent article's seller_info includes all sellers from every previous article. As your dataset grows, this array balloons uncontrollably and eats up all your memory.

The fix is straightforward: move the $seller_array declaration inside your first while loop, so it resets to empty for each new article.

Here's the corrected snippet of that section:

while ($row = mysqli_fetch_assoc($result_article)) {
    // Reset seller array for each new article
    $seller_array = array(); 
    $query_seller = "SELECT `SC_SELLER_NAME`, `SC_SELLER_COUNT`, `SC_GROSS_PRICE`,`SC_NET_PRICE`, `SC_CONV_GROSS`, `SC_CONV_NET`, `SC_DELIVERY_PRICE`, `SC_CURRENCY`, `SC_LAST_UPDATED` FROM `SC_SELLER_TBL` WHERE `SC_SELLER_INSERT` = '1' AND SC_AMA_ASIN = '" .$row['SC_ASIN_CD']. "' AND SC_CUST_PROD_CODE = '" .$row['SC_CUST_PROD_CODE']. "' ORDER BY `SC_SELLER_NAME`";
    $result_seller = mysqli_query($conn,$query_seller);
    while ($row1 = mysqli_fetch_assoc($result_seller)) {
        $seller_arr = array (
            "seller_name" =>$row1['SC_SELLER_NAME'],
            "gross_price_seller" =>$row1['SC_GROSS_PRICE'], 
            "net_price_seller" =>$row1['SC_NET_PRICE'], 
            "conv_gross_price_seller" =>$row1['SC_CONV_GROSS'], 
            "conv_net_price_seller" =>$row1['SC_CONV_NET'], 
            "delivery_price" =>$row1['SC_DELIVERY_PRICE'], 
            "currency" =>$row1['SC_CURRENCY'], 
            "last_updated" =>$row1['SC_LAST_UPDATED']
        );
        array_push($seller_array, $seller_arr);
    }
    // Rest of your article array building code...
}

2. Long-Term Optimization: Fix the N+1 Query Problem #

Even with the immediate fix, your code is still doing an inefficient N+1 query pattern: 1 query to get all articles, then 1 query per article to get its sellers. For large datasets, this is slow and puts unnecessary load on your database (and uses more memory than needed).

Instead, we can fetch all articles and all relevant sellers in just two queries, then map the sellers to their respective articles. Here's how to do it:

Step 1: Fetch all articles first #

$conn = mysqli_connect(dbhost, dbuser, dbpass, db);
mysqli_set_charset($conn,"utf8");
$company_id = Get_Company();

// Fetch all articles first
$query_article = "SELECT B.SC_PRODUCT_NAME,A.SC_CUST_PROD_CODE, A.SC_ASIN_CD,A.SC_ARTICLE_ID,A.SC_COMPANY_ID,A.SC_PROD_GIVEN_NAME,A.SC_LAST_CHECKED,A.SC_LAST_UPDATED,A.SC_DEFAULT_SELLER,A.SC_BUY_BOX_SELLER,A.SC_CURRENCY,A.SC_LAST_PRICE,A.SC_CONV_PRICE,A.SC_NET_PRICE,A.SC_CONV_NET,A.SC_PRICE_INC,A.SC_PRICE_DEC,A.SC_COUNTRY_CODE,A.SC_DOMAIN,A.SC_AVAILABLE,A.SC_AVAIL_DESCR,A.SC_PRICE_TIME,A.SC_FAULT_FLAG,A.SC_FAULT_TIME,A.SC_FAULT_MSG FROM `SC_PRICE_HIST_TBL` A INNER JOIN `SC_PRODUCT_TBL` B ON A.SC_CUST_PROD_CODE = B.SC_CUST_PROD_CODE WHERE `SC_PRICE_HIST_STATUS` = '1' AND `SC_PRICE_HIST_INSERT` = '1' AND A.SC_COMPANY_ID = '$company_id' ORDER BY B.SC_PRODUCT_NAME,`SC_ARTICLE_ID`";
$result_article = mysqli_query($conn,$query_article);

// Store articles in an array, keyed by a unique identifier (ASIN + product code)
$articles = [];
$articleKeys = [];
while ($row = mysqli_fetch_assoc($result_article)) {
    $key = $row['SC_ASIN_CD'] . '|' . $row['SC_CUST_PROD_CODE'];
    $articles[$key] = $row;
    $articleKeys[] = "('" . mysqli_real_escape_string($conn, $row['SC_ASIN_CD']) . "', '" . mysqli_real_escape_string($conn, $row['SC_CUST_PROD_CODE']) . "')";
}

Step 2: Fetch all sellers in one query #

// Fetch all sellers for the articles we found
if (!empty($articleKeys)) {
    $query_sellers = "SELECT `SC_AMA_ASIN`, `SC_CUST_PROD_CODE`, `SC_SELLER_NAME`, `SC_SELLER_COUNT`, `SC_GROSS_PRICE`,`SC_NET_PRICE`, `SC_CONV_GROSS`, `SC_CONV_NET`, `SC_DELIVERY_PRICE`, `SC_CURRENCY`, `SC_LAST_UPDATED` FROM `SC_SELLER_TBL` WHERE `SC_SELLER_INSERT` = '1' AND (SC_AMA_ASIN, SC_CUST_PROD_CODE) IN (" . implode(',', $articleKeys) . ") ORDER BY `SC_SELLER_NAME`";
    $result_sellers = mysqli_query($conn, $query_sellers);
    
    // Group sellers by the same ASIN + product code key
    $sellersByKey = [];
    while ($row1 = mysqli_fetch_assoc($result_sellers)) {
        $key = $row1['SC_AMA_ASIN'] . '|' . $row1['SC_CUST_PROD_CODE'];
        $seller_arr = array (
            "seller_name" =>$row1['SC_SELLER_NAME'],
            "gross_price_seller" =>$row1['SC_GROSS_PRICE'], 
            "net_price_seller" =>$row1['SC_NET_PRICE'], 
            "conv_gross_price_seller" =>$row1['SC_CONV_GROSS'], 
            "conv_net_price_seller" =>$row1['SC_CONV_NET'], 
            "delivery_price" =>$row1['SC_DELIVERY_PRICE'], 
            "currency" =>$row1['SC_CURRENCY'], 
            "last_updated" =>$row1['SC_LAST_UPDATED']
        );
        $sellersByKey[$key][] = $seller_arr;
    }
}

Step 3: Build your final article array #

$article_array = [];
foreach ($articles as $key => $row) {
    $seller_array = $sellersByKey[$key] ?? []; // Use empty array if no sellers
    $Article_Info=array(
        "product_name" => $row['SC_PRODUCT_NAME'],
        "product_code"=>$row['SC_CUST_PROD_CODE'],
        "ASIN"=>$row['SC_ASIN_CD'],
        "article_id"=>$row['SC_ARTICLE_ID'],
        "URL"=>$row['SC_DEFAULT_SELLER'],
        "default_seller"=>$row['SC_DEFAULT_SELLER'], 
        "gross_price"=>$row['SC_LAST_PRICE'], 
        "net_price"=>$row['SC_NET_PRICE'], 
        "conv_gross_price"=>$row['SC_CONV_PRICE'], 
        "conv_net_price"=>$row['SC_CONV_NET'], 
        "currency"=>$row['SC_CURRENCY'], 
        "domain"=>$row['SC_DOMAIN'], 
        "country"=>$row['SC_COUNTRY_CODE'], 
        "buy_box_seller"=>$row['SC_BUY_BOX_SELLER'], 
        "total_num_seller"=>count($seller_array), // Replace hardcoded 5 with actual count
        "seller_info"=>$seller_array
    );
    array_push($article_array, $Article_Info);
}

$jsonDataEncoded1 = json_encode($article_array,JSON_UNESCAPED_UNICODE|JSON_INVALID_UTF8_IGNORE);
echo $jsonDataEncoded1;
die();

3. Bonus: Additional Tips to Prevent Future Issues #

• Use Prepared Statements: Right now, your code is vulnerable to SQL injection. Switching to prepared statements will fix that and improve query performance.
• Free Up Result Sets: After fetching data from a result set, call mysqli_free_result($result) to release memory used by the result set.
• Adjust Memory Limit (Sparingly): If you still need more memory after optimizing, you can increase the limit with ini_set('memory_limit', '512M'), but this should be a last resort—optimizing your code is always better.
• Batch Processing: If your dataset is extremely large, consider processing it in batches (e.g., fetch 100 articles at a time) instead of loading everything into memory at once.

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