CODEWITHSUNDEEP
javascriptphpmysqlsql
illogic
illogic

Reputation: 240

MySQL : Create table comparison product

I am confused when will display product comparison table. I have 2 tables t_product_item and t_product_specification. Here's a picture of the table structure: pic 1

I want to display a product comparison like this picture :

pic 2

Script:

<table border="1">
    <tr style="background-color: #C3C3C3">
        <td>Product</td>
        <td>Spec Name</td>
        <td>Spec Value</td>
    </tr>
    <?php
        $sql = mysqli_query($conn, "SELECT item, spec_name, spec_value FROM t_product_item JOIN t_product_specification USING (item_id)");
        if (mysqli_num_rows($sql)>0){
            while ($row=mysqli_fetch_array($sql)){
    ?>
    <tr>
        <td><?php echo $row['item']?>
        <td><?php echo $row['spec_name']?>
        <td><?php echo $row['spec_value']?>
    </tr>
    <?php
        }}
    ?>
</table>

Instead it appears like this

Result: pic 3

How do I structure logically or query for the table to display like the example pic?

Upvotes: 1

Views: 605

Answers (2)

DineshDB
DineshDB

Reputation: 6193

Change your SQL Query to:

SELECT spec_name,MAX(CASE WHEN ItemId=1 THEN spec_value END)`Samsung Galaxy S8+`
    ,MAX(CASE WHEN ItemId=2 THEN spec_value END)`Samsung Galaxy S8` 
FROM t_product_item JOIN t_product_specification USING (item_id)
GROUP BY spec_name
ORDER BY MIN(spec_Id)

Hope this helps you.

Upvotes: 1

dommmm
dommmm

Reputation: 908

You are looping through item_id for each of your <tr> rows. Instead you should be looping through spec_name for <tr>, and for each cell <td> loop through the product.

In pseudo code:

    <table>
      <thead>
        <tr>
          <th>&nbsp;</th> <!-- Empty cell for spacer -->

          for (item in item_list) {
            <th>item.name</th>
          }

        </tr>
      </thead>

      <tbody>
        <tr>

          for (spec in spec_list) {
            <td>spec.name</td>
          }

          for (item in item_list) {
            <td>item.spec[spec.name]</td> <!-- display spec detail of each item -->
          }

        </tr>
      </tbody>
    </table>

You might have to restructure your data before looping it through the table.

Upvotes: 0

Related Questions