CODEWITHSUNDEEP
php
user3651819
user3651819

Reputation: 497

Display each color separate with values

I am having trouble in printing values from 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 values in table(for each color print material dimension category) So 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 query

$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 fetch all values in table, but that is not actually I want to make. 

<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 first color and then all material related to that color. I am having trouble there, any help or advice is appreciated?

Upvotes: 4

Views: 98

Answers (4)

Halayem Anis
Halayem Anis

Reputation: 7785

You have to ORDER BY 'COLOR' and not to GROUP BY 'COLOR'
After that yout test if you are switching 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>

UPDATE
If you can not change your query, so you have to order by COLOR your resultset

Upvotes: 1

Code Lღver
Code Lღver

Reputation: 15603

First remove the colorname field from group by clause in query and add this column with order by means add order by colorname in query.

And then change from HTML and php code with the following:

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

Upvotes: 1

Loko
Loko

Reputation: 6679

Check if the current colorname is equal to the previous one:

<?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++;
} ?>

Upvotes: 0

user1231342435346354
user1231342435346354

Reputation: 47

The easiest approach is to make one query for getting all colors, then you can make loop for result from first query and use another query for getting all information related to specific color.

Example: 

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

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

And include in this query all html you need for creating table. I hope this will help you.

Upvotes: -1

Related Questions