Color the row of a HTML table from a cell value (from an SQL server)-CodePudding

I am trying to create a code which colors the row/line of a specific table depending on a cell`s value. The data comes from a Microsoft SQL server

For example: if the cell named "files_link" is with a value 0 - color the whole line red (or if it is possible, color another row from that line red)

The idea is if the cell is:

0 - red color

1 - green color

2 - yellow

The idea (half working code)

Sadly I cannot add a working version in the code snippet because I am getting the data from an SQL server.

$(function() {
    $('tr > td:odd').each(function(index) {
        var scale = [['no', 0], ['yes', 1]];
        var score = $(this).text();
        for (var i = 0; i < scale.length; i  ) {
            if (score <= scale[i][1]) {
                $(this).addClass(scale[i][0]);
            }
        }
    });
});

table {
    width: 20em;
}

#score {
    width: 50%;
}

#name {
    width: 50%;
}

th {
    border-bottom: 2px solid #000;
    padding: 0.5em 0 0.1em 0;
    font-size: 1.2em;
}

td {
    border-bottom: 2px solid #ccc;
    padding: 0.5em 0 0.1em 0;
}

th:nth-child(even),
td:nth-child(even) {
    text-align: center;
}

.yes {
    background-color: #b6d8b0;
}

.half {
    background-color: #ffc16f;
}

.no {
    background-color: #CC5959;
}

<html>
<style>
td{text-align: center;}
.yes {
    background-color: #b6d8b0;
}

.half {
    background-color: #ffc16f;
}

.no {
    background-color: #CC5959;
}
</style>
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="jo.js"></script>
<title>AV.31.U</title>
</head>

<body>
<table style="width: 100%; height:50%" border="1" cellpadding="3">
<caption>AV.31.U</caption>
<tr>
            <td><div id="current_date"></p>
<script>
date = new Date();
year = date.getFullYear();
month = date.getMonth()   1;
day = date.getDate();
document.getElementById("current_date").innerHTML = day   "/"   month   "/"   year;
</script></td>
            <td colspan="6">Home</td>
            <td>&nbsp;</td>
        </tr>
<tr>
    <th>Проект
    <th>Машина
    <th>te
    <th>video_link
    <th>te
    <th>files_link
    <th>te
    <th>draw_link
</tr>
<?php
$username = 'censored';
$password = 'censored';
$servername = 'censored';
$database = 'censored';
  ini_set('display_errors', '1');
  error_reporting(E_ALL);
 $db = odbc_connect("Driver={SQL Server};Server=$servername;Database=$database;", $username, $password) or die ("could not connect<br />");

  $stmt = "Select * from machine";

  $result = odbc_exec($db, $stmt);

  if ($result == FALSE) die ("could not execute statement $stmt<br />");

  while (odbc_fetch_row($result)) // while there are rows
  {
     print "<tr>\n";
     print "  <td>" . odbc_result($result, "project_ID") . "\n";
     print "  <td>" . odbc_result($result, "machine_project_ID") . "\n";
     print "  <td>" . odbc_result($result, "video_link") . "\n";
     print "  <td>" . odbc_result($result, "files_link") . "\n";
     print "  <td>" . odbc_result($result, "draw_link") . "\n";
     print "  <td>" . odbc_result($result, 'has_draw') . "\n";
     print "  <td>" . odbc_result($result, "has_video") . "\n";
     print "  <td>" . odbc_result($result, "has_files") . "\n";
     print "</tr>\n";
  }

  odbc_free_result($result);

  odbc_close($db);
?>



</table>
</body>
</html>

CodePudding user response：

Easiest would be to add a class with PHP in your while loop.

In your PHP code:

$colorclass = [0 => 'red', 1 => 'green', 2 => 'yellow'];
while (odbc_fetch_row($result)) // while there are rows
{
   $classtext = '';
   $x = odbc_result($result, "has_video");
   if( in_array($x, $colorclass) )
   {
      $classtext = " class='".$colorclass[$x]."'";
   }
   print "<tr>\n";
   print "  <td>" . odbc_result($result, "project_ID") . "\n";
   print "  <td>" . odbc_result($result, "machine_project_ID") . "\n";
   print "  <td>" . odbc_result($result, "video_link") . "\n";
   print "  <td>" . odbc_result($result, "files_link") . "\n";
   print "  <td>" . odbc_result($result, "draw_link") . "\n";
   print "  <td>" . odbc_result($result, 'has_draw') . "\n";
   print "  <td".$classtext.">" . odbc_result($result, "has_video") . "\n";
   print "  <td>" . odbc_result($result, "has_files") . "\n";
   print "</tr>\n";
}

In your CSS:

.red { background: red; }
.green { background: green; }
.yellow { background: yellow; }

CodePudding user response：

I'm not a PHP developer, but if I understand the question right, you want to color the whole table row background if it contains a "0" value to red, "1" value to green, etc.?

In this case you could create a function in php which could add a custom attribute to that specific row like this:

<table>
    <tr my-attribute="0">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr my-attribute="1">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr my-attribute="2">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr my-attribute="1">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
</table>

and for the css you could easily select this row like this:

tr[my-attribute="0"]{
  background-color: red;
}
tr[my-attribute="1"]{
  background-color: green;
}
tr[my-attribute="2"]{
  background-color: yellow;
}

CodePudding user response：

First, place data in the <tbody> of the table. Second, dates can be formatted as Strings using toLocaleDateString. This function uses the Intl.DateTimeFormat formatting options.

Now, you can get the value of the seventh column of each row and set a data attribute i.e. data-has-link. You can declare styles for rows i.e. <td> with a particular data attribute. This is much easier to work with than abusing classes.

$(function() {
  var $table = $('table');
  $table.find('#current_date').text(getCurrentDate());
  colorRows($table); // Color the rows!
});

function getCurrentDate () {
  return new Date().toLocaleDateString('en-GB', {
    year: 'numeric',
    month: 'numeric',
    day: 'numeric'
  });
}

function colorRows($table) {
  var hasLink;
  $table.find('tbody > tr').each(function() {
    var i = parseInt($(this).find('td:nth-child(7)').text(), 10);
    $(this).attr('data-has-link', cellHasLink(i));
  });
}

function cellHasLink(value) {
  switch (value) {
    case 0  : return 'no';
    case 1  : return 'yes';
    default : return 'maybe';
  }
}

table { width: 20em; border-collapse: collapse; }

th {
  border-bottom: 2px solid #000;
  padding: 0.5em 0 0.1em 0;
  font-size: 1.2em;
}

td {
  border-bottom: 2px solid #ccc;
  padding: 0.5em 0 0.1em 0;
}

th:nth-child(n   2),
td:nth-child(n   2) {
  text-align: center;
}

tr[data-has-link="no"]    { background-color: #F77; }
tr[data-has-link="yes"]   { background-color: #7F7; }
tr[data-has-link="maybe"] { background-color: #FD7; }

#score, #name {  width: 50%; }

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table style="width: 100%; height:50%" border="1" cellpadding="3">
  <caption>AV.31.U</caption>
  <thead>
    <tr>
      <td><div id="current_date"></div></td>
      <td colspan="6">Home</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <th>Project</th>
      <th>Machine</th>
      <th>te</th>
      <th>Video Link</th>
      <th>te</th>
      <th>File Link</th>
      <th>te</th>
      <th>Draw Link</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>22005</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <td>22012</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>1</td>
      <td>1</td>
      <td>1</td>
    </tr>
    <tr>
      <td>22012</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>1</td>
      <td>3</td>
      <td>1</td>
    </tr>
  </tbody>
</table>

Modifying existing PHP

Modify your PHP to wrap the table header and body appropriately.

<table style="width: 100%; height:50%" border="1" cellpadding="3">
  <caption>AV.31.U</caption>
  <thead>
    <tr>
      <td><div id="current_date">
      <script>
      date = new Date();
      year = date.getFullYear();
      month = date.getMonth()   1;
      day = date.getDate();
      document.getElementById("current_date").innerHTML = day   "/"   month   "/"   year;
      </script></td>
      <td colspan="6">Home</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <th>Проект</th>
      <th>Машина</th>
      <th>te</th>
      <th>video_link</th>
      <th>te</th>
      <th>files_link</th>
      <th>te</th>
      <th>draw_link</th>
    </tr>
  </thead>
  <tbody>
    <?php
    $username = 'censored';
    $password = 'censored';
    $servername = 'censored';
    $database = 'censored';

    ini_set('display_errors', '1');
    error_reporting(E_ALL);

    $db = odbc_connect("Driver={SQL Server};Server=$servername;Database=$database;", $username, $password) or die ("could not connect<br />");

    $stmt = "Select * from machine";
    $result = odbc_exec($db, $stmt);

    if ($result == FALSE) die ("could not execute statement $stmt<br />");

    while (odbc_fetch_row($result)) // while there are rows
    {
      print "<tr>\n";
      print "  <td>" . odbc_result($result, "project_ID") . "\n";
      print "  <td>" . odbc_result($result, "machine_project_ID") . "\n";
      print "  <td>" . odbc_result($result, "video_link") . "\n";
      print "  <td>" . odbc_result($result, "files_link") . "\n";
      print "  <td>" . odbc_result($result, "draw_link") . "\n";
      print "  <td>" . odbc_result($result, 'has_draw') . "\n";
      print "  <td>" . odbc_result($result, "has_video") . "\n";
      print "  <td>" . odbc_result($result, "has_files") . "\n";
      print "</tr>\n";
    }

    odbc_free_result($result);
    odbc_close($db);
    ?>
  </tbody>
</table>