[SOLVED] Color Coding a specific Table Column from MYSQL output.

[cuboidgraphix]

Greetings again,

I have a php/mysql page that outputs an html table with field names and data rows.  I want to make it a lil more fancy by color coding a specific column. In this html table it's the last column, which is a percentage column.  I want to color code the number according to value whereby if it's more than 2.999 % it's red, if it's between 1.5 and 2.999 it's orange if it's below 1.5 percent it's green ... and so forth.

My problem is that I can't find a way to target a specific column.  On my script below, for instance, the IF statement targets all the columns.  Can someone please help me out?

 

The output part of my script is as follows:

 

<?php
print "<table width=\"800\">";	
print "<tr>";						
for($i = 0;$i < mysql_num_fields($data);$i++)
    {
    print "<th>" . mysql_field_name($data, $i) . "</th>";				
    } 
print "</tr>";			
for ($i = 0; $i < mysql_num_rows($data); $i++)
    {
    print "<tr>";
    $row = mysql_fetch_row($data);
        for($j = 0;$j < mysql_num_fields($data);$j++) 
            {
            if($row[$j] >= 2.9999)
                {
                echo("<td class=\"red\">" . $row[$j] . "</td>");
                }
            else
                {
                echo("<td>" . $row[$j] . "</td>");
                }
            }	
    print "</tr>";							
    } 
print "</table>";
?>

[JD*]

Try this:

 

 

<?php
print "<table width=\"800\">";	
print "<tr>";						
for($i = 0;$i < mysql_num_fields($data);$i++)
    {
    print "<th>" . mysql_field_name($data, $i) . "</th>";				
    } 
print "</tr>";			
for ($i = 0; $i < mysql_num_rows($data); $i++)
    {
    print "<tr>";
    $row = mysql_fetch_row($data);
        for($j = 0;$j < mysql_num_fields($data);$j++) 
            {
              if(mysql_field_name($row[$j]) == "your_field_name_here")
              {
                if($row[$j] >= 2.9999)
                {
                  $class=" class=\"red\"";
                } 
            } 
echo("<td $class>" . $row[$j] . "</td>");
}
    print "</tr>";							
    } 
print "</table>";
?>

[GingerRobot]

Or, if you know you want the last field:

 

<?php
print "<table width=\"800\">";	
print "<tr>";
$num_fields = mysql_num_fields($data);						
for($i = 0;$i < $num_fields;$i++)
    {
    print "<th>" . mysql_field_name($data, $i) . "</th>";				
    } 
print "</tr>";			
for ($i = 0; $i < mysql_num_rows($data); $i++)
    {
    print "<tr>";
    $row = mysql_fetch_row($data);
        for($j = 0;$j < $num_fields;$j++) 
            {
            if($j = $num_fields){//last column
                if($row[$j] >= 2.9999) 
                    $class = 'red';
                elseif($row[$j] >= 1.5) 
                    $class = 'orange';
                else
                    $class = 'green';
            }else{
                $class = '';
            }
            echo("<td class=\"$class\">" . $row[$j] . "</td>");
            }	
    print "</tr>";							
    } 
print "</table>";
?>

<?php
print "<table width=\"800\">";	
print "<tr>";						
for($i = 0;$i < mysql_num_fields($data);$i++)
    {
    print "<th>" . mysql_field_name($data, $i) . "</th>";				
    } 
print "</tr>";			
for ($i = 0; $i < mysql_num_rows($data); $i++)
    {
    print "<tr>";
    $row = mysql_fetch_row($data);
        for($j = 0;$j < mysql_num_fields($data);$j++) 
            {
            if($row[$j] >= 2.9999)
                {
                echo("<td class=\"red\">" . $row[$j] . "</td>");
                }
            else
                {
                echo("<td>" . $row[$j] . "</td>");
                }
            }	
    print "</tr>";							
    } 
print "</table>";
?>

[craygo]

can use something like this

 

<?php
function bgcolor($field, $value){
  if($field == "sort"){
    if($value >= "2.99"){
    $bgcolor = "red";
    } else {
      if($value < "2.99" && $value > "1.5"){
      $bgcolor = "orange";
      } else {
      $bgcolor = "green";
      }
    }
  } else {
  $bgcolor = "";
  }
return $bgcolor;
}
?>

 

Now call it in your loop

<?php
print "<table width=\"800\">";
print "<tr>";
for($i = 0;$i < mysql_num_fields($data);$i++)
    {
    $fieldname =  mysql_field_name($data, $i);
    print "<th>" .$fieldname . "</th>";
    }
print "</tr>";
for ($i = 0; $i < mysql_num_rows($data); $i++)
    {
    print "<tr>";
    $row = mysql_fetch_row($data);
        for($j = 0;$j < mysql_num_fields($data);$j++)
            {
            $fieldname =  mysql_field_name($data, $j);
                echo("<td bgcolor=\"".bgcolor($fieldname, $row[$j])."\">" . $row[$j] . "</td>");

            }
    print "</tr>";
    }
print "</table>";
?>

 

Now you can add other fields in the function if you want other rows to be color coded. I used bgcolor just for test but you can apply the value to whatever you want.

 

Ray

[cuboidgraphix]

Thanks a lot guys, I really appreciate all your inputs...

 

Craygo I think I like yours best.  I think I can surely work with yours more easily.

 

Thanks again everybody.

[cuboidgraphix]

Hi Craygo,

 

Something came up...  What if I wanted to target 2 or more field names, each having a different color code?  How could I do that? I tried doing... 

 

<?php
function bgcolor($field, $value){
  if($field == "sort1"){
    if($value >= "2.99"){
    $bgcolor = "red";
    } else {
      if($value < "2.99" && $value > "1.5"){
      $bgcolor = "orange";
      } else {
      $bgcolor = "green";
      }
    }
  } else {
  $bgcolor = "";
  }
return $bgcolor;
  if($field == "sort2"){
    if($value >= "90"){
    $bgcolor = "green";
    } else {
      if($value < "80" && $value > "70"){
      $bgcolor = "orange";
      } else {
      $bgcolor = "red";
      }
    }
  } else {
  $bgcolor = "";
  }
return $bgcolor;
}
?>

 

This didn't work... It only color codes the "sort1".. Can I have your input on this?

 

Please and thanks in advance.

[craygo]

What happened is you are returning the bgcolor right after the first if statement. Since you want to look for one field at a time you have to chain the if/then's. Might be better to use a switch instead

<?php
function bgcolor($field, $value){
  switch ($field){
  case "sort1":
  if($value >= "2.99"){
  $bgcolor = "red";
  } else {
    if($value < "2.99" && $value > "1.5"){
    $bgcolor = "orange";
    } else {
    $bgcolor = "green";
    }
  }
  break;
  case "sort2":
  if($value >= "90"){
  $bgcolor = "green";
  } else {
    if($value < "80" && $value > "70"){
    $bgcolor = "orange";
    } else {
    $bgcolor = "red";
    }
  }
  break;
  default:
  $bgcolor = "";
  }
return $bgcolor;
}
?>

 

Ray

[cuboidgraphix]

Thanks a lot Ray...

 

That solved the problem...  You're the best. 

[craygo]

No Problem don't forget to mark the tread as solved.

 

 

Ray