Duplicated values in mysql/php/json array output joining 3 tables-CodePudding

I swear I tried everything I could but with no success, I read every answer in the whole platform but I can't get rid of duplicates (or loop repetitions)

My tables are:

items

item_id (primary, AI, unique), it_title, etc. (the only important for this is "item_id")

atributes

atr_id (primary, AI, unique), item_atr_id (the related column with item_id in items table) and other atribute data fields like color, etc.

gallery

img_id (primary, AI, unique), item_gal_id (the related column with item_id in items table), file_name, etc.

After the query I use an array to json_encode it.

THE FIRST STEP

When joining 2 tables there is no problem, I have no duplicates but is weird what happens in the array anyway

$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();

OUTPUT USING THIS ARRAY

$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);

data (json output)

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

Note:images array is getting the same qty of atributes even when in this case I'm not joining images table yet so I guess there is something a bit wrong in the array...

THE PROBLEM

When I join images table with this query...

$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();

the output has a lot of duplicates (not really duplicates, repetitions by a loop)

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"

When the real data in the DB is 2 atributes and 4 images for the item I'm showing as example, the ID 00939 (not for each atribute)

So, I tried DISTINCT, RIGHT JOIN's, OUTER JOIN's, subqueries and (surely wrong) GROUP BY. I think the way is using GROUP BY or a subquery but I really can't get it after 3 days...

Thnak you for your time.

CodePudding user response：

You're creating a cross product between all the attributes and gallery items.

The simplest solution is to do two 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);

The second query doesn't even need to join with items, since you already got all the items into the $data array from the first query.

CodePudding user response：

So, thanks to Barmar the final code (SOLUTION) I used is this one:

FIRST QUERY:

$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();

SECOND QUERY:

$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();

And the code for the OUTPUT:

$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);

Note: for those who need this output as json don't forget to use

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

in you file