skip to Main Content

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%20eMesUeR-2%20ocE%20onredauC-1580569182.jpg"
item_gal_id "00939"
1   
id  "6722"
filename    "https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno-eco2-reingreso%2Cjpg-1662146124.jpg"
item_gal_id "00939"
2   
id  "6725"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno%2Cjpg-1615840256.jpg"
item_gal_id "00939"
3   
id  "6723"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/eMesUeR-2%20ocE%20onredauC-1580569180.jpg"
item_gal_id "00939"
4   
id  "6722"
filename    "https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno-eco2-reingreso%2Cjpg-1662146124.jpg"
item_gal_id "00939"
5   
id  "6724"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/ellated%20eMesUeR-2%20ocE%20onredauC-1580569182.jpg"
item_gal_id "00939"
6   
id  "6723"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/eMesUeR-2%20ocE%20onredauC-1580569180.jpg"
item_gal_id "00939"
7   
id  "6725"
filename    " https://zecat-user-images-prod.s3.amazonaws.com/generic_products/cuaderno%2Cjpg-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.

2

Answers


  1. Chosen as BEST ANSWER

    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


  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search