Show entry when value drops

[karenn1]

My table looks as follows:

 

Bianca  Chair Theatre  Peter Stuyvesant Extra Mild HL Lights  16  60 
Bianca  Chair Theatre  Peter Stuyvesant Filter Soft Pack  56  10
Bianca  Chair Theatre  Peter Stuyvesant Menthol HL Lights  23  15
Bianca  Club 151  Rothmans Mint HL Lights  27  25
Bianca  Club 151  Rothmans Rich HL Filter  50  50
Bianca  Club 151  Rothmans Special Mild HL  47  15
Elton  Sin Bar  Kent 1  17
Elton  Sin Bar  Kent 5  18
Elton  Sin Bar  Kent 8  19

 

This is a result of SQL coding and a while loop on my table. On the same table, I have a script that highlights the cell red when the value in the second column goes below 20. What I want to do is have all three entries for a specific venue (either Chair Theatre, Club 152, etc) display when there is a value below 20 present in one entry. If there's no value below 20 for that venue, it shouldn't display. A venue can have more than three entries so it can't be hard coded. My table coding looks as follows:

 

<?php
		$i = 0;
		while ($rs_prop = mysql_fetch_array($result_prop))	{
	?>
            <tr <?php if (is_int($i/2)) print "bgcolor=\"#558ED5\""; ?>>
              <td style="padding-left:5px; padding-right:2px;" width="49" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["TMR_Name"]); ?>
              </span></td>
              <td style="padding-left:5px; padding-right:2px;" width="112" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["Venue_Name"]); ?>
              </span></td>
              <td style="padding-left:5px; padding-right:2px;" width="181" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["L_Code_Code"]); ?>
              </span></td>
              <td <?php if ($rs_prop['Stock'] < $rs_prop['Low_Stock']) print "bgcolor=\"red\""; if ($rs_prop['Stock'] == $rs_prop['Balance']) print "bgcolor=\"yellow\""; ?> width="51" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["Stock"]); ?>
              </span></div></td>
              <td width="50" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["Low_Stock"]); ?>
              </span></div></td>
              <td width="50" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["High_Stock"]); ?>
              </span></div></td>
              <td <?php if ($rs_prop['Stock'] > $rs_prop['Low_Stock']) { if ($rs_prop['HS_Percentage'] < 50) print "bgcolor=\"yellow\""; }?> width="50" height="23" bgcolor="#E9EDF4" class="body4">
               <div align="right">
                    <?= ucwords($rs_prop["HS_Percentage"]); ?>
                </div></td>
              <td width="69" height="23" bgcolor="#E9EDF4" class="body4">
                <div align="right">
                  <?= ucwords($rs_prop["Balance"]); ?>
                </div></td></tr>
            <?php $i++; } ?>

 

Does this make sense? Can anybody help?

 

Thanks,

Karen

[wildteen88]

Something like this:

<?php
		$i = 0;
		while ($rs_prop = mysql_fetch_array($result_prop))	{
	?>
            <tr <?php if (is_int($i/2)) print "bgcolor=\"#558ED5\""; ?>>
              <td style="padding-left:5px; padding-right:2px;" width="49" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["TMR_Name"]); ?>
              </span></td>
              <td style="padding-left:5px; padding-right:2px;" width="112" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["Venue_Name"]); ?>
              </span></td>
              <td style="padding-left:5px; padding-right:2px;" width="181" height="23" bgcolor="#E9EDF4" class="style35"><span class="style10">
                <?= ucwords($rs_prop["L_Code_Code"]); ?>
              </span></td>
              <td <?php if ($rs_prop['Stock'] < $rs_prop['Low_Stock']) print "bgcolor=\"red\""; if ($rs_prop['Stock'] == $rs_prop['Balance']) print "bgcolor=\"yellow\""; ?> width="51" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["Stock"]); ?>
              </span></div></td>
              <td width="50" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["Low_Stock"]); ?>
              </span></div></td>
              <td width="50" height="23" bgcolor="#E9EDF4" class="body4"><div align="right"><span class="style10">
                <?= ucwords($rs_prop["High_Stock"]); ?>
              </span></div></td>
              <td <?php if ($rs_prop['Stock'] > $rs_prop['Low_Stock']) { if ($rs_prop['HS_Percentage'] < 50) print "bgcolor=\"yellow\""; }?> width="50" height="23" bgcolor="#E9EDF4" class="body4">
                <div align="right"<?=($rs_prop["HS_Percentage"] < 20) ? 'style="color: red"' : null ?>>
                    <?= $rs_prop["HS_Percentage"]; ?>
                </div></td>
              <td width="69" height="23" bgcolor="#E9EDF4" class="body4">
                <div align="right">
                  <?= $rs_prop["Balance"]; ?>
                </div></td></tr>
            <?php $i++; } ?>

[Barand]

You could use SQL to determine which are displayed. What column name are you checking for "< 20"?

 

You code seems to have far more columns listed than your example.

[karenn1]

It does have a lot more listed, I've just simplified it to explain it better. Basically, how it will work is that when stock at a certain venue drops below the low stock indicator, it should display red thus being an alert for the rep to go there and refill the stock. But if the venue has no alerts, it should not be shown. Does that make better sense?

 

 

 

Karen

[Barand]

SELECT s.venue, s.L_Code_code, s.stock
FROM stock s
INNER JOIN (SELECT venue, MIN(stock) as minstock FROM stock
                 GROUP BY venue HAVING minstock < 20) as X
ON s.venue = X.venue

 

The above query will list stock data only for those venues where there is a stock level below 20