CODEWITHSUNDEEP
phpmysqlcss
user2549007
user2549007

Reputation:

Highlighting a cell PHP a certain color based on MYSQL value

I have three values that can be displayed from a table/column in mysql, RED, GREEN, YELLOW for the field "ProspectStatus"

Is there anyway I can make a cell change its background color based on the value?

e.g.

echo "<td>" . $row['ProspectStatus'] . "</td>";

PHP Code:

 $result = mysql_query("SELECT * FROM customerdetails"); 
//List the Columns for the Report 
echo "<table border='1'> 
<tr> 
<th>CustomerID</th> 
<th>Customer Name</th> 
<th>Prospect Status</th> 
<th>Address</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['CustomerID'] . "</td>"; 
  echo "<td>" . $row['CustomerName'] . "</td>"; 
  echo "<td>" . $row['ProspectStatus'] . "</td>"; //this is the field I want to show either RED, GREEN or YELLOW 
  echo "<td>" . $row['Address'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>";

Upvotes: 6

Views: 21540

Answers (6)

Glitch Desire
Glitch Desire

Reputation: 15061

First, use CSS for this, not messy in-line style definitions

The most maintainable option would probably separate the color codes themselves from the view logic, so in CSS, create some new classes:

td.done { background-color: green; }
td.working { background-color: yellow; }
td.stopped { background-color: red; }

Then, in your loop just do this (you don't need the echo statements, anything outside of <? and ?> tags will be parsed as HTML rather than PHP):

<?php while($row = mysql_fetch_array($result)): ?>
<tr>
    ....
    <td class='<?= $row['ProspectStatus']; ?>'><?= $row['ProspectStatus']; ?></td>
    ....
</tr>
<?php endwhile; ?>

This way, if ProspectStatus is 'done', the cell will have a green background, if it's 'working', it will have a yellow background and if it's stopped it will have a red background.

If ProspectStatus is a range, or otherwise not this simple

Let's say ProspectStatus doesn't have done, working and stopped or similar simple values, and instead it's a range, for this you can use a simple nested ternary operator to get the color you want instead of <tr class='<?= $row['ProspectStatus']; ?>'>:

<tr class='<?php echo ($row['ProspectStatus'] >= 80) ? "done" : (($row['ProspectStatus'] >= 40) ? "working" : "stopped"); ?>'>

With this one liner, if ProspectStatus is greater than 80, the field will be green, if it's between 40 and 80 it will be yellow and if it's below 40 it will be red.

Note: mysql_ functions are DEPRECATED

By using the mysql_ features, you run the risk of your program not working in future versions of PHP as those functions have been formally deprecated as of 5.5.

The new recommended way to perform your query is this:

$mysqli = new mysqli("username","password","hostname","password");
$result = $mysqli->query("SELECT * FROM customerdetails");
while($row = $result->fetch_assoc())...

Hope this helps.

Upvotes: 2

Mark
Mark

Reputation: 8451

You can do this with this:

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['CustomerID'] . "</td>"; 
  echo "<td>" . $row['CustomerName'] . "</td>"; 
  if($row['ProspectStatus']=='[val1]') // [val1] can be 'approved'
         echo "<td style='background-color: #00FF00;'>".$row['ProspectStatus']."</td>"; 
  else if($row['ProspectStatus']=='[val2]')// [val2]can be 'rejected'
         echo "<td style='background-color: #FF0000;'>".$row['ProspectStatus']."</td>"; 
  else if($row['ProspectStatus']=='[val3]') //[val3] can be 'on hold'
         echo "<td style='background-color: #FFFF00;'>".$row['ProspectStatus']."</td>"; 
  echo "<td>" . $row['Address'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>";

The value of the status may depend on the color. I assume that val1, val2 and val3 are the values of the 3 colors.

Upvotes: 7

kevinm
kevinm

Reputation: 475

Since you said you want to change the color of only one particular cell, this should do the trick:

while($row = mysql_fetch_array($result)) 
      { 
      echo "<tr>"; 
      echo "<td>" . $row['CustomerID'] . "</td>"; 
      echo "<td>" . $row['CustomerName'] . "</td>"; 
      echo "<td  style='background-color: ". $row['ProspectStatus'] ."'>" . $row['ProspectStatus'] . "</td>";
      echo "<td>" . $row['Address'] . "</td>"; 
      echo "</tr>"; 
      }

Upvotes: 0

Tredged
Tredged

Reputation: 501

echo "<tr style='background-color: ". $row['ProspectStatus'] ."'>";

or make an if statement checking what color || then displaying the echo

That should work.

Upvotes: 0

liyakat
liyakat

Reputation: 11853

yes you can add it to you Table data tag like below

The following code will work:

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr style='background-color: ". $row['ProspectStatus'] ."'>"; 
  echo "<td>" . $row['CustomerID'] . "</td>"; 
  echo "<td>" . $row['CustomerName'] . "</td>"; 
  echo "<td>" . $row['ProspectStatus'] . "</td>"; //this is the field I want to show either RED, GREEN or YELLOW 
  echo "<td>" . $row['Address'] . "</td>"; 
  echo "</tr>"; 
  }

Upvotes: 0

aug
aug

Reputation: 11714

Create a class of the color you want for that cell and then make an if statement that will check the value. Depending on the value, echo that class.

Upvotes: 0

Related Questions