How do I retrieve a single row from the MySQL database and only display the columns that contain a value greater than zero

Question

I have a table on a MySQL database called quotes. This table contains a column called quote_id, I use a select query to retrieve an array where the quote_id matches the id of the quote being edited.

"SELECT * FROM quotes WHERE quote_id = '$quote_id'";

The remaining columns in the table contain the various products labeled by their product code.

The other table called items contain each product with its product code as model, description and price.

Each time a new quote is created it is inserted into the table with a unique quote ID. The value below each column is the quantity required for the quote.

What I am trying to achieve is using a query to retrieve a specific row from the database, and only display the value greater than 0.

As an example:

As you can see I am able to achieve this but the table rows that are not showing affect the spacing between rows.

The if statement I used for each table row is as follows:

    <tr>
        <td>
             <?php if ($kx_t7765x >0 ){ echo "KX-T7765";} ?>
        </td>
        <td>
             <?php if ($kx_t7765x >0 ){ echo "Doorphone";} ?>
        </td>
        <td>
             <?php if ($kx_t7765x >0 ){ echo $kx_t7765x;} ?>
        </td>
    </tr>

I am sure there must be an easier way to go about doing this instead of coding this for each and every table row required, and Im sure using a while statement would be the correct way to go about this.

Answer 1

First off, this database is a mess. With an hour's work you could make it far more reasonable. There should be a pivot table linking quotes to items, not a separate column for each item. You have to rebuild the entire table every time you decide to sell a new item? That's not normal.

The simple answer to your immediate problem is to make the <tr> element conditional, not the contents of the <td> elements.

<?php if ($kx_t7765x > 0): ?>
<tr>
    <td>KX-T7765</td>
    <td>Doorphone</td>
    <td><?=$kx_t7765x?></td>
</tr>
<?php endif; ?>

Really, all those table cells should be dynamically entered from the items table as well.