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
So, thanks to Barmar the final code (SOLUTION) I used is this one:
FIRST QUERY:
SECOND QUERY:
And the code for the OUTPUT:
Note: for those who need this output as json don't forget to use
in you file
You’re creating a cross product between all the attributes and gallery items.
The simplest solution is to do two sep
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.