echo multiple fields belong to one field, without printing the one field many times php

Question

I have few fields that matches one field. for eample I have the fiels size as an attribute but its has many fields that match to it. I want to echo something like: size: s, m, l, xl, xxl... but when I print it, it goes like: size: s size: m size: l size: xl..

how can I print it correctly? code:

<?php

// Create connection
$conn = new mysqli('localhost', 'root','','catalog');
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql="SELECT DISTINCT categories.title AS title_categories , attributes.title AS title_attributes,  labels_atr.title AS title_labels_atr
FROM categories
INNER JOIN attributes ON attributes.id=categories.id
INNER JOIN labels_atr ON labels_atr.id_atr=categories.id
GROUP BY labels_atr.title
";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
       extract($row);
        echo "category: ".$title_categories.",attributes: ".$title_attributes."<BR>";
        echo "labels_atr: ".$title_labels_atr."<BR>";
} 
}
else {
    echo "0 results";
}

$conn->close();
?>

output:

category: Pants,attributes: Size
    labels_atr: 32X32
    category: Pants,attributes: Size
    labels_atr: 32X34
    category: Pants,attributes: Size
    labels_atr: 32X36
    category: Pants,attributes: Size
    labels_atr: 34X32
    category: Pants,attributes: Size
    labels_atr: 34X36
    category: Pants,attributes: Size
    labels_atr: 36X32
    category: Sale,attributes: Size
    labels_atr: L
    category: Sale,attributes: Size
    labels_atr: M
    category: Sale,attributes: Size
    labels_atr: S
    category: Sale,attributes: Size
    labels_atr: XL
    category: Sale,attributes: Size
    labels_atr: XS
    category: Sale,attributes: Size
    labels_atr: XXL
    category: Shirts,attributes: Color
    labels_atr: Black
    category: Shirts,attributes: Color
    labels_atr: Blue
    category: Shirts,attributes: Color
    labels_atr: Grey
    category: Shirts,attributes: Color
    labels_atr: Orange
    category: Shirts,attributes: Color
    labels_atr: Red
    category: Shirts,attributes: Color
    labels_atr: White
    category: Shirts,attributes: Color
    labels_atr: Yellow

Would you prefer to accomplish this with your query or by using PHP? — showdev, Feb 24 '20 at 11:06
it doesnt mutter to me, as long as the result will be good, and I wiil be able to do an html of this, so that the size for exmaple will be the title and the s,m,l,ect. wiil be the fields ander the title — , Feb 24 '20 at 11:11
I've never found one good compelling reason to use `extract()` in any of my projects in over 10 years of programming. I would advise against it here - no real benefit. — mickmackusa, Feb 24 '20 at 11:35

GMB · Answer 1 · 2020-02-24T11:30:26.753

0

If I followed you correctly, you can use (proper) aggregation and GROUP_CONCAT():

SELECT 
    c.title AS title_categories , 
    a.title AS title_attributes,  
    GROUP_CONCAT(l.title) AS title_labels_atrs
FROM categories c
INNER JOIN attributes a ON a.id = c.id
INNER JOIN labels_atr l ON l.id_atr = c.id
GROUP BY c.title, a.title

edited Feb 24 '20 at 11:30

answered Feb 24 '20 at 11:13

GMB

216,147
25
84
135

its still print every title multiple times – Feb 24 '20 at 11:35
You should use GMB's advice and figure out why your implementation isn't working. This is the most appropriate technique. – mickmackusa Feb 24 '20 at 11:59

echo multiple fields belong to one field, without printing the one field many times php

1 Answers1