mysql/php/json陣列輸出中的重復值連接3個表-有解無憂

我發誓我盡我所能但沒有成功，我閱讀了整個平臺上的每一個答案，但我無法擺脫重復（或回圈重復）

我的桌子是：

專案

item_id (primary, AI, unique), it_title, etc. (唯一重要的是"item_id")

屬性

atr_id（primary、AI、unique）、item_atr_id（items表中與item_id相關的列）以及顏色等其他屬性資料欄位。

畫廊

img_id（primary、AI、unique）、item_gal_id（items表中與item_id相關的列）、file_name等。

查詢后，我使用一個陣列對其進行 json_encode。

第一步

加入 2 個表時沒有問題，我沒有重復但奇怪的是陣列中發生了什么

$rs_items = $conn->prepare("
    SELECT a.*, b.* 
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    ORDER BY a.item_id ASC , b.atr_id ASC");
$rs_items->execute();

使用此陣列的輸出

$data = array();
    
foreach ($rs_items->fetchAll(PDO::FETCH_ASSOC) as $row) {
    
    
    if (!isset($data[$row['item_id']])) {
         
        $data[$row['item_id']] = [
            'ID' => $row['item_id'],
            'name' => $row['it_title'],
            'atributes' => [],
            'images' => [],
                
        ];
    }
        
    $data[$row['item_id']]['atributes'][] = [
        'id' => $row['atr_id'],
        'item_prod_id' => $row['item_atr_id'],
        'color' => $row['val1'],
        'stock' => $row['stock'],
    ];
        
    $data[$row['item_id']]['images'][] = [
        'id' => $row['img_id'],
        'filename' => $row['file_name'],
        'item_gal_id' => $row['item_gal_id'],
    ];
        
        
}
$response = array();
$response['data'] =  $data;
//print_r($response);
    
echo json_encode($response, JSON_PRETTY_PRINT);

資料（json輸出）

0   
    ID  "00939"
    name    "Notebook"
    
    
    atributes   
        0   
        id  "140925"
        item_prod_id    "00939"
        color   "Black"
        stock   "12975"
        1   
        id  "140926"
        item_prod_id    "00939"
        color   "Blue"
        stock   "2326"
    images  
        0   
        id  null
        filename    null
        item_gal_id null
        1   
        id  null
        filename    null
        item_gal_id null

注意：即使在這種情況下我還沒有加入影像表時，images 陣列也會獲得相同數量的屬性，所以我猜陣列中有一些錯誤......

問題

當我用這個查詢加入影像表時......

$rs_items = $conn->prepare("
    SELECT a.*, b.*, c.* 
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    LEFT JOIN gallery c ON a.item_id = c.item_gal_id 
    ORDER BY a.item_id ASC , b.atr_id ASC");
$rs_items->execute();

輸出有很多重復（不是真的重復，回圈重復）

data
    
    
ID  "00939"
name    "Notebook"

atributes   
0   
id  "140925"
item_prod_id    "00939"
color   "Black"
stock   "12975"
1   
id  "140926"
item_prod_id    "00939"
color   "Blue"
stock   "2326"
2   
id  "140925"
item_prod_id    "00939"
color   "Black"
stock   "12975"
3   
id  "140926"
item_prod_id    "00939"
color   "Blue"
stock   "2326"
4   
id  "140925"
item_prod_id    "00939"
color   "Black"
stock   "12975"
5   
id  "140926"
item_prod_id    "00939"
color   "Blue"
stock   "2326"
6   
id  "140925"
item_prod_id    "00939"
color   "Black"
stock   "12975"
7   
id  "140926"
item_prod_id    "00939"
color   "Blue"
stock   "2326"


images  
0   
id  "6724"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/ellated eMesUeR-2 ocE onredauC-1580569182.jpg"
item_gal_id "00939"
1   
id  "6722"
filename    "https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno-eco2-reingreso,jpg-1662146124.jpg"
item_gal_id "00939"
2   
id  "6725"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno,jpg-1615840256.jpg"
item_gal_id "00939"
3   
id  "6723"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/eMesUeR-2 ocE onredauC-1580569180.jpg"
item_gal_id "00939"
4   
id  "6722"
filename    "https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno-eco2-reingreso,jpg-1662146124.jpg"
item_gal_id "00939"
5   
id  "6724"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/ellated eMesUeR-2 ocE onredauC-1580569182.jpg"
item_gal_id "00939"
6   
id  "6723"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/eMesUeR-2 ocE onredauC-1580569180.jpg"
item_gal_id "00939"
7   
id  "6725"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno,jpg-1615840256.jpg"
item_gal_id "00939"

當資料庫中的真實資料是我作為示例顯示的專案的2 個屬性和4 個影像時，ID 00939（不是每個屬性）

所以，我嘗試了 DISTINCT、RIGHT JOIN、OUTER JOIN、子查詢和（肯定是錯誤的）GROUP BY。我認為方法是使用 GROUP BY 或子查詢，但 3 天后我真的無法得到它......

謝謝你的時間。

uj5u.com熱心網友回復：

您正在創建所有屬性和圖庫專案之間的交叉產品。

最簡單的解決方案是做兩個 sep

$rs_items = $conn->prepare("
    SELECT a.*, b.* 
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    ORDER BY a.item_id ASC , b.atr_id ASC");
$rs_items->execute();

$data = array();
    
foreach ($rs_items->fetchAll(PDO::FETCH_ASSOC) as $row) {
    if (!isset($data[$row['item_id']])) {
        $data[$row['item_id']] = [
            'ID' => $row['item_id'],
            'name' => $row['it_title'],
            'atributes' => [],
            'images' => [],
        ];
    }
        
    if ($row['atr_id'] !== null) {
        $data[$row['item_id']]['atributes'][] = [
            'id' => $row['atr_id'],
            'item_prod_id' => $row['item_atr_id'],
            'color' => $row['val1'],
            'stock' => $row['stock'],
        ];
    }
}

$rs_items = $conn->prepare("
    SELECT *
    FROM gallery
    ORDER BY gal_id ASC");
$rs_items->execute();

foreach ($rs_items->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $data[$row['item_gal_id']]['images'][] = [
        'id' => $row['img_id'],
        'filename' => $row['file_name'],
        'item_gal_id' => $row['item_gal_id'],
    ];
}

$response = array();
$response['data'] =  $data;
//print_r($response);
    
echo json_encode($response, JSON_PRETTY_PRINT);

第二個查詢甚至不需要加入 with items，因為您已經$data從第一個查詢中將所有專案放入陣列中。

uj5u.com熱心網友回復：

所以，感謝 Barmar，我使用的最終代碼（解決方案）是這個：

第一個查詢：

$rs_items = $conn->prepare("
    SELECT a.*, b.* 
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    ORDER BY a.item_id ASC , b.atr_id ASC");
$rs_items->execute();

第二個查詢：

$rs_items_images = $conn->prepare("
    SELECT a.*, c.* 
    FROM items a 
    LEFT JOIN gallery c ON a.item_id = c.item_gal_id 
    ORDER BY c.item_gal_id ASC");
$rs_items_images->execute();

以及輸出的代碼：

$data = array();
            
        foreach ($rs_items->fetchAll(PDO::FETCH_ASSOC) as $row) {
            
        if (!isset($data[$row['item_id']])) {
                 
                $data[$row['item_id']] = [
                    'ID' => $row['item_id'],
                    'name' => $row['it_title'],
                    'atributes' => [],
                    'images' => [],
                        
                ];
            }
                
            $data[$row['item_id']]['atributes'][] = [
                'id' => $row['atr_id'],
                'item_prod_id' => $row['item_atr_id'],
                'color' => $row['val1'],
                'stock' => $row['stock'],
            ];
                       
        }
        
        // Barmar solution
        foreach ($rs_items_images->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $data[$row['item_id']]['images'][] = [
                'id' => $row['img_id'],
                'filename' => $row['file_name'],
                'item_gal_id' => $row['item_gal_id'],
            ];
        }
    
    $response = array();
    $response['data'] =  $data;
    //print_r($response);
        
    echo json_encode($response, JSON_PRETTY_PRINT);

注意：對于那些需要此輸出為 json 的人，不要忘記使用

header("Content-Type:application/json");

在你的檔案中

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/510790.html

標籤：phpmysql数组加入通过...分组

上一篇：通過Where子句MySQL計算分組行

下一篇：選擇不同表中相同條目的計數