0

I'd like to create a query with GROUP BY, however not all rows are shown in the result. Here is an example of the database:

id     | color  | size | price
010101 |  orange|  S   | 11 |
010102 | orange |  M   | 11 |
010103 | orange |  L   | 11 |   
010201 | green  |  S   | 11 |
etc.

I want to group the results by color. However, only one size shows up. This is what it looks like:

Casual Text Shirt
 Orange 
S
Green 
S
... etc.

Only S shows up, not M or L.

This is my code:

<? php
if (isset($_POST['01'])){
    echo "Casual Text Shirt<br>";

try {
$db=new PDO((all db info here));
$query = $db->prepare("SELECT color, size , price FROM shirts WHERE id LIKE '01%' GROUP BY color");
$query->execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);

foreach($result as &$data) {

echo "". $data["color"] . "</div><br>";
echo"". $data["size"] . "<br> ";
echo $data["price"] . " €<br><br>";
}
    } catch(PDOException $e) {
        die("Error!: " . $e->getMessage());
}
}
?>
caroll ann c
  • 3
  • 2
  • `GROUP BY color` means you combine all the rows with the same color into a single row. I think you want `ORDER BY color` so all those rows will be next to each other. – Barmar May 27 '21 at 22:52
  • You can group by multiple columns. Eg. `SELECT color, size , price FROM shirts WHERE id LIKE '01%' GROUP BY color, size` – kenneth_oe May 27 '21 at 23:09
  • @Barmar I'd like to have something like this be the result: Orange - S M L Green - S M etc. And not have the color show up each time a new size is given – caroll ann c May 27 '21 at 23:35
  • See https://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 – Barmar May 27 '21 at 23:43

0 Answers0