Display each color separately with values

Question

I am having problems printing values ​​from a database.

ITEM TABLE

ITEM | COLOR | MATERIAL | DIMENSIONS | CATEGORY | QUANTITY
- 01    33      05           111         12         1000.00
- 02    33      07           125         18         200.00
- 03    33      11           156         18         254.00
- 04    56      15           25          66         113.00
- 05    66      05           11          33         521.00

I am trying to print the values ​​in a table (for each dimension category of a color printed material) Thus, the output will be:

COLOR - > 33
MATERIAL | DIMENSION | CATEGORY  | QUANTITY
05          111          12          1000.00
07          125          18          200.00
11          156          18          254.00

COLOR - > 56
MATERIAL | DIMENSION | CATEGORY  | QUANTITY
15          25           66          113.00

COLOR - > 66
MATERIAL | DIMENSION | CATEGORY  | QUANTITY
05          11           33          521.00

I am using a request

$query = "SELECT a.itemnb, b.colorname, c.materialname, d.categoryname, sum(a.quantity) as quantity 
FROM dbo_items a
JOIN dbo_color b
ON a.color=b.colorid
JOIN dbo_material c
on a.material=c.material
JOIN dbo_category
on a.category=d.categoryid
GROUP BY b.colorname, c.materialname, d.categoryname, ";

I am using PDO.

$q=$conn->query($query);

Now I can get all the values ​​in the table, but I really do not want this.

<table class="table table-bordered table-striped">
  <thead>
    <tr class="bg-primary">
      <td data-field="color">COLOR</td>
      <td data-field="material">MATERIAL</td>
      <td data-field="dim">DIMENSIONS</td>
      <td data-field="quantity">QUANTITY</td>
    </tr>
  </thead>
  <tbody>  
  <?php while ($r = $m->fetch()){?> 
    <tr>
      <td><?=$r['colorname']?></td>
      <td><?=$r['materialname']?></td>
      <td><?=$r['categoryname']?></td>
      <td><?=$r['quantity ']?></td>
   <?php } ?>
  </tbody>
</table>

I want to print the first color, and then all the material related to that color. I have problems there, any help or advice is appreciated?

Answer 1

You need ORDER BY "COLOR", not GROUP BY "COLOR"
After that, you will test the test if you switch to a new color

<?php
<table class="table table-bordered table-striped">
  <thead>
    <tr class="bg-primary">
      <td data-field="color">COLOR</td>
      <td data-field="material">MATERIAL</td>
      <td data-field="dim">DIMENSIONS</td>
      <td data-field="quantity">QUANTITY</td>
    </tr>
  </thead>
  <tbody>  
  <?php 
    $prevColor = '';
    while ($r = $m->fetch()){?> 
    <tr>
      <td><? print ($prevColor == $r['colorname'] ? '' :  $r['colorname']) ?></td>
      <td><? print $r['materialname']?></td>
      <td><? print $r['categoryname']?></td>
      <td><? print $r['quantity ']?></td>
   <?php 
        $prevColor = $r['colorname'];} 
    ?>
  </tbody>
</table>

, COLOR

Answer 2

colorname group by order by order by colorname .

HTML php :

<table class="table table-bordered table-striped">
  <thead>
    <tr class="bg-primary">
      <td data-field="color">COLOR</td>
      <td data-field="material">MATERIAL</td>
      <td data-field="dim">DIMENSIONS</td>
      <td data-field="quantity">QUANTITY</td>
    </tr>
  </thead>
  <tbody>  
  <?php 
  $tempColor = '';
  while ($r = $m->fetch()){
  if($tempColor != $r['colorname']) {
   ?>
   <tr><td colspan="4">Color Name: <?=$r['colorname']?></td></tr>
  <?php $tempColor = $r['colorname'];
  } else { 
  ?> 
    <tr>
      <td><?=$r['colorname']?></td>
      <td><?=$r['materialname']?></td>
      <td><?=$r['categoryname']?></td>
      <td><?=$r['quantity ']?></td>
   <?php }
   }
    ?>
  </tbody>
</table>

Answer 3

, colorname :

<?php
$currcolor=array();
$i=0;
while ($r = $m->fetch()){
    $currcolor[$i]=$r['colorname']; ?> 
    <tr>
      <td>
      <?php if($currcolor[$i] != $currcolor[$i-1]){
          echo $currcolor[$i];
      } ?>
      </td>
      <td><?php echo $r['materialname']; ?></td>
      <td><?php echo $r['categoryname']; ?></td>
      <td><?php echo $r['quantity ']; ?></td>
   <?php
    $i++;
} ?>

Answer 4

- , , .

:

$q = 'SELECT * FROM dbo_color';
$q=$conn->query($query);

while ($res = $q->fetch()){
   $secondQ = 'SELECT * FROM relatedMaterials WHERE color = ' . $res->color;
}

html, . , .